Grade Formulas in Excel

by Gregory on November 20, 2010

Student Scores

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)

Grade Lookup Table

GradeLU

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.

Define Name dialog box

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.

Related Posts Plugin for WordPress, Blogger...
Eric November 22, 2010 at 4:21 am

Why not use

=CHOOSE(1+INT(Score/10),”F”,”F”,”F”,”F”,”F”,”F”,”D”,”C”,”B”,”A”,”A”)

But the LOOKUP works good if you grade on a curve.

Gregory November 22, 2010 at 9:20 am

I thought about using CHOOSE, but was worried about the values from 1 to 10. You’ve solved that problem by adding a one (1) to INT(Score/10) so thanks for that.

Kevin Law May 11, 2013 at 9:44 am

I get an value error message when attempting to employ GradeLookup as a constant named array. What am I doing wrong? More details might help.

Gregory May 14, 2013 at 7:16 pm

I’ll send you my worksheet. That should give you more detail.

RP May 22, 2013 at 2:07 pm

I have the same problem as Kevin when I try to use GradeLookup as a constant. I defined the constant array as stated and set up the VLookup as described, but I get the “#Value!” error. The formula seems to treat the constant array as one complete term, I think, since when I check the name it has inserted not only the “=” sign but also quotation marks around the array.

Would you mind describing how to use a constant (named) array in a little more detail than given above, since it seems like this may be a relatively common problem for people who are trying to replicate your method? I am sure it would be greatly appreciated since this is a very useful tip!

Thanks!
RP

Gregory May 27, 2013 at 1:09 pm

You probably have a slight, yet imperceptible error in the constant formula, which is very easy to do. I’ve updated the post to add the working Excel file that I referenced when I wrote the post. You can download my file and compare it to what you’ve done to see the difference.

I’ve added the file link here for your convenience.

Comments on this entry are closed.

{ 2 trackbacks }

Previous post:

Next post: