0

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.

Improve this question
3
  • 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, 2013 at 15:26
  • 2
    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, 2013 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, 2013 at 18:38

3 Answers 3

Reset to default
3

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)))))

Improve this answer
2

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))))

Improve this answer
1

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)))))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .