MS Excel Value Range Equivalent (IF Statement)

Question

What is wrong with my formula?

=if(((F2 >= 97) and (f2 <= 100)), "Excellent", if(((f2 >= 94) and (f2 <= 96)), "Good", "None of the Above"))

i am creating a worksheet that displays it equivalent grade. Here is the range:

97 - 100  ==>  Excellent
94 -  96  ==>  Good
and so on and so forth...

or is there a better way to do this?

Answer 1

or is there a better way to do this?

You can use LOOKUP to shorten the formula. You need to specifiy the lower bound of each range, e.g.

=IF(F2="","",LOOKUP(F2,{0,70,80,90,94,97},{"Poor","Average","Above Average","Below Good","Good","Excellent"}))

.....best way IMO would be to put all the values in a table, so using my example you could list 0, 70, 80 etc. in Y2:Y7 and the associated text in Z2:Z7 and then formula is simply

=IF(F2="","",LOOKUP(F2,$Y$2:$Z$7))

That's much easier to maintain and you can easily change the breakpoints or text etc. by changing the table, without touching the formulas