Excel IF statement Not returning the appropriate Value

Question

I'm trying to grade students by giving them A or B depending on their score. If someone is having absent instead of a score, I return a value of the cell.

However, it does not return the value of the cell. The reference records are in a separate sheet called raw. I think it may be because I'm trying to return a string data.

I am using Excel 2007. Here's the formula:

=IF(raw!E6>=75,"AA",IF(raw!E6>=70,"AB",IF(raw!E6>=60,"B",IF(raw!E6>=50,"C",IF(raw!E6>=40,"D",IF(raw!E6<40,"RT",raw!E6))))))

Answer 1

Don't use Nested IFs if you can avoid it. Instead, use a banded VLOOKUP: it's many times more efficient, and a heck of a lot simpler to troubleshoot. Something like this:

=IF(ISNUMBER([@Score]),VLOOKUP([@Score],Table1,2,TRUE),"Absent")

Notes:

• The above uses Tables and the associated Table Notation. I always use Tables when I can, because they reduce spreadsheet administration and the Structured Table References have intrinsic meaning.
• The VLOOKUP must have TRUE as the forth argument, and the lookup table must be sorted in ascending order.
• The lowest score must be zero, so that anything below 40 gets a "Retake" grade.