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.

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.