IF AND Formula for range created...Why is decimal number not recognized

[jste123]

Good Afternoon,

Hope someone has a better eye than me today. I am missing something and I just don't see it. I have created a formula to return a specific text value if the number in a cell is within a range. i.e.,

A1 is input cell. Range is 0-48 = "A", 49-59 = "B", 60-70 = "C", 71-81 = "D", 82+ = "F"

My formula is: =IF(AND(A1<=48,A1>=0),"A",IF(AND(A1<=59, A1>=49),"B", IF(AND(A1<=70, A1>=60),"C",IF(AND(A1<=81,A1>=71),"D", "F")))).

For the most part it works but when I enter 59.2 in A1 I get "F". If I enter 59.0 in A1 I get "B". Shouldn't 59.2 be recognized as 59. If I enter 58.7 I get "B". Please help!!!

[Ace_XL]

Your formula does not account for the decimals in between ranges, hence gives the residual "F" value.

Try

=LOOKUP(A1,{0,49,60,71,82},{"A","B","C","D","F"})

[Tony Valko]

Create a table like this:

Data Range

	C	D	E
1	From	To	Grade
2	0	48	A
3	49	59	B
4	60	70	C
5	71	81	D
6	82	82+	F

Then the formula would be:

=LOOKUP(A1,C1:E6)