How to Calculate Grade Percentage in Excel (2 Suitable Ways)

We have a grade sheet of a student with obtained marks in five distinct subjects. We want to calculate the grade percentage considering the letter grading system and the grading sheet.

Dataset to Calculate Grade Percentage in Excel

Method 1 – Using the VLOOKUP Function to Calculate the Grade Percentage

VLOOKUP function looks for a lookup value or a range of lookup values in the leftmost column of a defined lookup array and then returns a specific value from the index column number of the lookup array based on the exact or partial match.

The syntax of the VLOOKUP function is:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Part 1.1 – Getting the Letter Grade and Percentage for Each Subject Separately

Steps

Insert the following function in E5.

=C5/D5

Press Enter and you will get the result in decimal format.
Click the Percent Style icon in the Number group of the Home tab.

Select the Fill Handle tool and drag it down to Autofill the formula.

You’ll get grade percentages for all subjects.

We moved the grading table to the range D12:E18.
Use the formula below in F5.

=VLOOKUP(E5,$D$12:$E$18,2,TRUE)

The formula locks in the search array reference with the dollar signs ($) to make it into an absolute reference. This allows that part of the formula to stay when copying the formula to other rows or columns.

Press Enter.

Formula Unlocking

The VLOOKUP function looks for the cell value of E5 (84%) in the lookup array $D$12:$E$18.

After finding the value in the specified range of the array, it takes the value of the second column (as we have defined column index 2) for an approximate match (argument: TRUE) of that array in the same row of the lookup value and returns the result in the selected cell.

Output=> A.

Drag the formula down and the letter grades for all subjects will be shown.

Part 1.2 – Calculating the Average Grade Percentage and Average Letter Grade in Excel

Steps

Add two extra columns named Average Grade Percentage and Average Letter Grade to the previous data set.
Apply the AVERAGE function to calculate the average letter grade of all the subjects.

=AVERAGE(E5:E9)

Apply the VLOOKUP function to find the Average Letter Grade:

=VLOOKUP(G5,D12:E18,2,TRUE)

Method 2 – Using a Nested IF Formula to Calculate the Grade Percentage in Excel

Steps

Select F5 and apply the following formula to create a condition to find the letter grade:

=IF(E5<40%, $E$12, IF(E5<50%, $E$13, IF(E5<60%, $E$14, IF(E5<70%, $E$15, IF(E5<80%, $E$16, IF(E5<90%, $E$17, $E$18))))))

Formula Unlocking

We’re using the Nested IF function to add multiple conditions to meet our criteria.

If the value in Cell E5 does not meet the first condition, it’ll go to the next IF, and so on. Once this process fulfills the condition for E5, the fixed Letter Grade from the cells (E12:E18) will be assigned to it.

Drag the formula for the other cells and you’ll get the expected results.

Grade Percentage Calculator

We are providing a grade percentage calculator in the Excel file. Input the values in the marked area and this calculator will automatically calculate the grade percentage and show you the letter grade.