Nested IF Function Not Working

[rstephen]

Hi there,

I am trying to get a nested function to work for me and I have seriously been working on it off-and-on for hours.

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. This is how the bonus works:

Average 0-299 yards/game = 0 points
Average 300-399 yards/game = 4 points
Average 400-499 yards/game = 6 points
Average 500+ yards/game = 8 points

This is what I have, but it is not working. Can you help!?! Thanks in advance!! NOTE: D4 is the cell where the player's AVERAGE YARDS PER GAME will be listed and of course this formula is going in the cell under the BONUS column.

=IF(D4>499,8,IF((300<=D4<=399),4,IF((400<=D4<=499),6,0)))

0-499 just gets me 0 in the cell, but of course for 300-399 I want 4 and for 400-499 I want 6. 500+ DOES give me a value of 8, however.

THANKS!!!

[NBVC]

TRy:

=LOOKUP(D4,{0,300,400,500},{0,4,6,8})

[rstephen]

YOU'RE AWESOME!!! Thanks a bunch!

[oeldere]

I solved this problem with VLookup (see example).

The yellow cell is the answer.

[rstephen]

I solved this problem with VLookup (see example).

The yellow cell is the answer.

Thanks to you as well !

[oeldere]

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?
If you have (other) questions, just ask.

[rstephen]

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?
If you have (other) questions, just ask.

Thank for letting me know. I didn't know I had to mark it solved. First day on here. This is a really handy forum.

[NBVC]

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?
If you have (other) questions, just ask.

I guess you mean Glad we could help

[oeldere]

@NBVC

Please Login or Register  to view this content.

Of course glad we could help.

In #5 the OP refers to my anser, so I ansered back.

Sorry for cousing trouble with my statement.