Nested IF statement not need help

  • Thread starter Thread starter wtamustudentmc
  • Start date Start date
W

wtamustudentmc

Help - nested IF statement not bringing back correct value

Cell A18=85 (result/value is SUM of other cells)
Cell
A19=IF(A18>93,"A",IF(A18>88,"AB",IF(A18>83,"B",IF(A18>78,"BC",IF(A18>73,"C",IF(A18>68,"CD",IF(A18>63,"D","F")))))))

Unfortunately cell A19 is bringing back A .... not the correct value of B.
Can anyone tell me what's wrong?
 
I pasted the formula into A19, entered 85 in A18 and got the result B in A19,
as expected..

I think you have a text value in A18
Test with
=ISNUMBER(A18) in any cell

If you get FALSE then that is your problem...

Format the cell as Number... enter 85 again and your formula will work...
 
Create a 2 column table like this:

0.........F
64.......D
69......CD
74......C
79......BC
84......B
89.....AB
94.....A

Assume that table is in the range A1:B8

Then use this formula to get the grade:

=IF(A18="","",VLOOKUP(A18,A1:B8,2))
 
Hi,

A lookup approach makes for a much shorter formula:

Create a range like

0 F
64 D
69 CD
74 C
79 BC
84 B
89 AB
94 A

You can adjust the first column depending on have your grades break out,
that is if your grade are things like 78.21 then you many need use 79.01 or
you may need 79.000001. Suppose the above table is in A1:C8 then the formula
would be

=LOOKUP(A18,A1:B8)

or without a lookup table:

=LOOKUP(A18,{0,"F";64,"D";69,"CD";74,"C";79,"BC";84,"B";89,"AB";94,"A"})
 
Back
Top