The Easy Way to Calculate Grades in Excel

An updated version of this post has been published on my new site: http://easy-excel.com/?p=730

 

How can we calculate the grades (A-F) in Excel if we have the test results as numbers? We know that a score of 90% or higher is an A, 80-89% is a B, 70-79% is a C, 65-69% is a D and less than 65% is an F.

First, we need to organize this information in a lookup table:

It is important that the table is sorted in ascending order, with the numbers to the left and the letters to the right.

Now we can use the VLOOKUP function to look up the value in the left column and return the value in the right column.

The VLOOKUP function looks for a value in a table and returns another value on the same row in that table. You can look for an exact match, but in this case we want to find an approximate match, e.g. if the score is greater than or equal to 70%, but less than 80%, the student gets a C.

Let’s type a formula in C2 and see which grade Samantha got:

=VLOOKUP(B2,$E$2:$F$6,2)

The first argument in this formula, B2, is the result. The second argument, $E$2:$F$6, is the lookup table. Don’t forget the dollar signs (shortcut: F4) to lock the reference in order to be able to copy the formula down. The third argument, 2, is the number of the column that has the value we’re looking for. VLOOKUP also allows for a fourth argument; exact match or approximate match. In this case we want an approximate match, which is default for this function, so we don’t have to specify it in the formula.

Copy the formula down, and the grade report is done!

But what if you are not allowed to sort the grading scale and it looks like this:

Neither VLOOKUP nor INDEX+MATCH will work here. We have to use a nested IF-function.

=IF(B2>$F$2,$E$2,IF(B2>$F$3,$E$3,IF(B2>$F$4,$E$4,IF(B2>$F$5,$E$5,$E$6))))

Explanation:

If B2 (95%) is greater than F2 (90%), return the value in E2 (A)
If not, check if B2 is greater than F3 (80%) and return the value in E3 (B)
If not, check if B2 is greater than F4 (70%) and return the value in E4 (C)
If not, check if B2 is greater than F5 (65%) and return the value in E5 (D)
If not, return the value in E6 (F)

I would not recommend this solution unless it’s absolutely necessary. It’s difficult to write the formula, especially if you have a larger table than in this example, and the risk of error is a lot higher than if you use the VLOOKUP method.