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.

Dataset to Calculate Grade Percentage in Excel

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

Type Formula to Calculate Grade Percentage in Excel

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

Calculate Grade Percentage in Excel

  • You’ll get grade percentages for all subjects.

Grade Percentage

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.

Letter Grade in Excel

Read More: Make an Excel Spreadsheet Automatically Calculate Percentage


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

Calculate average Grade Percentage in Excel

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.

Read More: How to Calculate Cumulative Percentage in Excel


Grade Percentage Calculator

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.


Download Practice Workbook

You can download our workbook to practice yourself which we’ve used to prepare this article.


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!


Related Articles


<< Go Back to Percentage Formula ExamplesCalculating Percentages | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo