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

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel provides a number of fruitful & easy methods to calculate grade percentages. Here I’m going to show you the techniques with proper illustrations through which you’ll be able to calculate grade percentage in Excel from a set of specific data & then assign them to text strings based on some fixed criteria.

Let’s say, we have a grade sheet of a student describing obtained marks in 5 distinct subjects. We want to calculate the grade percentage considering the obtained marks on the respective grade sheet. In the right-side chart, there’s a letter grading system mentioned under all ranges of marks percentages. In this section, you will find 2 suitable methods to calculate grade percentage in Excel. We will use two Excel built-in functions to serve our purpose. Let’s discuss them with proper illustrations here.

## 1. Using Excel VLOOKUP Function to Calculate 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])`

We will apply this function to search for the obtained marks in the predefined letter grade range.

We will determine two things-

• Grade Percentages for All Subjects
• Letter Grades for All Subjects

### 1.1. Getting Letter Grade and Percentage for Each Subject Separately

Let’s calculate the grade percentage for the student from the concerned dataset. In order to demonstrate the process, follow the steps below.

Steps

• First of all, let’s say, we want to find out the grade percentage of Math. So, select a cell where you want to show the grade percentage of Math and type the formula below in the selected cell.
`=C5/D5`

Here,

• C5 = Marks Obtained
• D5 = Total Marks • Now, press ENTER and you will get the result in decimal format.
• So, you have to convert it to the Percent Style format. Just take your cursor to the Percent Style icon in the Number group of the Home tab as stated in the image below, • Now, select this Fill Handle tool => Drag it down to Autofill the formula and release the mouse button. • You’ll get grade percentages for all subjects. Let’s move to the 2nd part now. We have to find Letter Grade for each subject now.

• First, we need to find out the letter grade for math only. Type the formula below in a selected cell.
`=VLOOKUP(E5,\$D\$12:\$E\$18,2,TRUE)`

Here,

• E5= Lookup value which needs to be looked up in the grading system chart array
• D12:E18 = Lookup Array where grade percentages, as well as related letter grades, are inscribed
• 2 = 2nd column in that array which needs to be printed as a letter grade for a specific range of percentages
• TRUE = the approximate match you’re going to find, otherwise the particular grade percentage obtained in a subject will not be included inside the particular percentage range if an exact match is not gained In this formula, you have to lock the whole array by using the ‘\$’ symbol before each Row Number and Column Name. This is called absolute cell references & unless you lock the cell references here, the calculation will not return to this specific array every time in the lookup process & error messages, as well as misinterpreted results, will be shown for some data.

• Now, press ENTER and the cell will return you the letter grade for Math.

🔓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.

So, Output=> A.

• After that, drag the formula down and the letter grades for all subjects will be shown right away. ### 1.2. Calculating Average Grade Percentage and Average Letter Grade in Excel

Now let’s determine the average grade percentage & average letter grade for all subjects.

Steps

• Firstly, add two extra columns named Average Grade Percentage & Average Letter Grade to the previous data set.
• Now, apply the AVERAGE function to calculate the average letter grade of all the subjects.
`=AVERAGE(E5:E9)`

Here,

• E5:E9 = Range of values which average to be calculated Here, you’ll get the average grade percentage.

• Now, apply the VLOOKUP function once again to find the Average Letter Grade assigned to the Average Grade Percentage.
`=VLOOKUP(G5,D12:E18,2,TRUE)`

Here,

• G5= Lookup value
• D12:E18 = Lookup Array
• 2 = Column Index Number
• TRUE = the approximate match
• Press Enter & you’ll get the Average Letter Grade.

## 2. Using Nested IF Formula to Calculate Grade Percentage in Excel

We can gain similar results by using the Nested IF formula too if the VLOOKUP function seems a bit difficult to you. IF function induces a logical test. So here are the steps to use the nested IF function to find Letter Grades after finding out the Grade Percentages.

Steps

• First of all, select a cell 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 then it’ll waver around all conditions until it meets the exact criteria. Once this process fulfills the condition for E5, the fixed Letter Grade from the cells (E12:E18) will be assigned to it.

So, the Letter Grade for math will be A as it meets the condition

• Now, drag the formula for the other cells & you’ll get the expected results at once. Here, I am providing you with a grade percentage calculator in the Excel file. Just input values in the yellow marked area and this calculator will automatically calculate the grade percentage and show you the letter grade. ## Conclusion

These are some of the most effective methods to calculate Grade Percentages and then convert them to Letter Grades in Excel I’ve found. I hope this article has helped you to guide with proper instructions. If you have any questions, thoughts, or feedback you can comment here. Goodbye!

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  