Grade Formulas in Excel

There are several ways to turn student scores into letter grades. I recently came across a nested IF formula that did the trick, but it seemed rather complicated.

A better solution would be to use a VLOOKUP formula with a Grade Lookup Table, but then it occurred to me that the VLOOKUP formula could stand alone by using an Array Constant.

For all these solutions I’m using the following grade scale:

• 0 – 59 = F
• 60 – 69 = D
• 70 – 79 = C
• 80 – 89 = B
• 90 – 100 = A

The Nested IF Solution

Using the scale above as a guide, the following nested IF formula will turn a score from 0 to 100 into the correct letter grade.

=IF(Score>=90,”A”,IF(Score>=80,”B”,IF(Score>=70,”C”,IF(Score>=60,”D”,”F”))))

This formula uses Score, which is a named range that contains all the student scores.

A VLOOKUP with a Grade Lookup Table

The following formula will also give the correct letter grades.

=VLOOKUP(Score,GradeLU,2,TRUE)

This formula has four arguments. Score refers to the student score that’s being looked up.

GradeLU is the Grade Lookup Table that’s on another worksheet and is a named range.

The two (2) means that a number from the second column will be returned from GradeLU.

The TRUE means the student score will be approximately matched to the first column from GradeLU. This is what allows a score of 72 to be matched to 70 and consequently return a letter grade of C.

A VLOOKUP with an Array Constant

The Grade Lookup Table can be replaced with an array constant. In the formula above, the second argument, GradeLU, could be replaced with the following:

{0,”F”;60,”D”;70,”C”;80,”B”;90,”A”}

However, instead of typing this into the formula each time, we can create a Named Constant in the Define Name dialog box shown below.

By creating GradeLookup as a named constant array, the formula can be shortened to:

=VLOOKUP(Score,GradeLookup,2,TRUE)

This formula does not need to reference a separate Grade Lookup Table on a worksheet because all the values are located in the named array constant, GradeLookup, which is now located in Excel’s internal memory.

Create a Named Constant Array

Bring up the Define Name dialog box and type in the name GradeLookup. Then delete the contents in the Refers to: text box and type in {0,”F”;60,”D”;70,”C”;80,”B”;90,”A”} exactly and click OK. Excel will add the equals sign (=).

How you access the Define Name dialog box depends on the version of Excel your using:

• In Excel 2003, and Excel 2008 and 2011 for Mac, use the menu selection Insert-Name-Define…
• In Excel 2007 and 2010 click the Ribbon tab Formulas and select Define Name.

Download the File

You can download the file with this link.