# Nested IF statement

I'm having a problem with this code for nested =IF statement. The range is basically:

<=16 to get 5 pts
16-17 to get 4 pts
17-18 to get 3 pts
18-19 to get 2 pts
above 19 to get 1 pt

and my code is:

``````=IF(D50<=16,5,IF(AND(D50>16,D50<=17),4,IF(AND(D50>17,D50<=18),3,IF(AND(D50>18,D50<=19),2,IF(D50>19),1 ,0))))
``````

I think it's correct but it is still showing me errors.

• Have you even tried to troubleshoot this? MS pops up an error automatically, and highlights the problem part of your formula as the bracket/parenthesis after `D50>19`. – David Zemens May 14 '13 at 15:26
• I think that the one who decided this syntax for if clause in excell should go straight to hell. It quickly become unreadable... – Laurent S. May 14 '13 at 15:26
• I think it would be wise to ignore the opinion of a person who can't spell a simple word like "Excel" while complaining that the product is too difficult to work with. @Bartdude – ExactaBox May 14 '13 at 18:38

Move the right bracket after the last 19 to the end of the function:

IF(D50<=16,5,IF(AND(D50>16,D50<=17),4,IF(AND(D50>17,D50<=18),3,IF(AND(D50>18,D50<=19),2,IF(D50>19,1 ,0)))))

The ANDs aren't really required - for example the first IF deals with any values <= 16, so for the next IF you don't need to specify > 16, that must be the case - similarly the last IF isn't required - it can't be FALSE because you have exhausted all other possibilities.

I would start at the other end of the spectrum, too - this formula should give the required results:

`=IF(D50>19,1,IF(D50>18,2,IF(D50>17,3,IF(D50>16,4,5))))`

Remove the ) after D50>19

``````=IF(D50<=16,5,IF(AND(D50>16,D50<=17),4,IF(AND(D50>17,D50<=18),3,IF(AND(D50>18,D50<=19),2,IF(D50>19,1,0)))))
``````