Table of Contents
Calculating letter grades in Excel can be done easily using the IF and VLOOKUP functions. First, assign numerical values to each letter grade (ex: A = 4, B = 3, C = 2, D = 1, F = 0). Then, use the IF function to set up a condition that checks the numerical value against a certain range (ex: IF score is between 90-100, return A). Finally, use the VLOOKUP function to match the numerical value with the corresponding letter grade. This will allow you to automatically generate letter grades based on numerical scores in Excel.
Calculate Letter Grade in Excel (With Examples)
Often you may want to convert a percentage grade to a letter grade in Excel.
There are two common ways to do so by using the IFS function in Excel:
Method 1: Calculate Letter Grade Using A, B, C, D, F
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")
Method 2: Calculate Letter Grade Using A, B, C, D, F with Plus or Minus
=IFS(B2>=97,"A+",B2>=93,"A",B2>=90,"A",B2>=87,"B+",B2>=83,"B",B2>=80,"B-",B2>=77,"C+",B2>=73,"C",B2>=70,"C-",B2>=67,"D+",B2>=63,"D",B2>=60,"D-",B2<60,"F")
The following examples show how to use each method in practice with the following dataset in Excel:
Example 1: Calculate Letter Grade Using A, B, C, D, F
We can type the following formula into cell C2 to calculate the letter grade for the first student:
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")
We can then click and drag this formula down to each remaining cell in column C:
Column C uses the following logic to assign a letter grade to each student based on their score:
- If the score is greater than or equal to 90, assign a letter grade of A.
- Else, if the score is greater than or equal to 80, assign a letter grade of B.
- Else, if the score is greater than or equal to 70, assign a letter grade of C.
And so on.
Note: Feel free to change the numeric values in the IFS function if you use different values to assign letter grades.
Example 2: Calculate Letter Grade Using A, B, C, D, F with Plus or Minus
We can type the following formula into cell C2 to calculate the letter grade with a plus or minus for the first student:
=IFS(B2>=97,"A+",B2>=93,"A",B2>=90,"A",B2>=87,"B+",B2>=83,"B",B2>=80,"B-",B2>=77,"C+",B2>=73,"C",B2>=70,"C-",B2>=67,"D+",B2>=63,"D",B2>=60,"D-",B2<60,"F")
We can then click and drag this formula down to each remaining cell in column C:
Column C uses the following logic to assign a letter grade to each student based on their score:
- If the score is greater than or equal to 97, assign a letter grade of A+.
- Else, if the score is greater than or equal to 93, assign a letter grade of A.
- Else, if the score is greater than or equal to 90, assign a letter grade of A-.
And so on.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: