Nested IF Function or LOOKUP Function

Hi there,

I posted a similar request last week but now I need to change the formula so need some more help! I was working on this for HOURS last week haha.

Basically I am setting up a fantasy sports spreadsheet that calculates everything for me. I have an AVERAGE YARDS PER GAME column and a BONUS column which gives the player extra bonus points if they average a certain amount of yards per game. I also have a GAMES PLAYED column. This is how the bonus works:

Average 0-249 yards/game = 0 points per game played
Average 250-274 yards/game = 4 points per game played
Average 275-299 yards/game = 6 points per game played
Average 300-324 yards/game = 8 points per game played
Average 325+ yards/game = 10 points per game played

I need the formula in the BONUS cell to pull the information in the AVERAGE YARDS PER GAME cell and determine if it is worth 0, 2, 4, 6, 8, or 10 bonus points and then multiply the bonus points it is worth by the number in the GAMES PLAYED cell. The BONUS cell (where the formula is going to go) is cell D4, the AVERAGE YARDS PER GAME cell is cell D3 and the GAMES PLAYED cell is D2.

Can someone please help, please!

THANKS!!

Originally Posted by vlady

hello, maybe...


=LOOKUP(averageyardpergame,{0,250,275,300,325},{0,4,6,8,10})*numberofgamesplayed

This works! THANKS!

Hi,

You need a VLookup function with the Range_Lookup = TRUE. See example 2 on http://www.excelfunctions.net/ExcelVlookup.html

I'm sure this would work too but the other option was much easier haha. Thanks anyways!

Have a good day!!