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

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.


Download Practice Workbook

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


2 Suitable Methods to Calculate Grade Percentage in Excel

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 VLOOKUP Function

VLOOKUP function looks for a lookup a 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. Calculate Letter Grade and Percentage for Each Subject Separately

Let’s calculate the grade percentage for the student from the concerning 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 and drag it down to Autofill the formula and release the mouse button.

Calculate Grade Percentage in Excel

  • Hence, 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 & 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 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: How to Calculate Subject Wise Pass or Fail with Formula in Excel


1.2. Calculate 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.

Read More: How to Calculate Percentage of Marks in Excel (5 Simple Ways)


Similar Readings


2. Inserting Nested IF Formula to Find Out 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, 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: Percentage Formula in Excel (6 Examples)


Grade Percentage Calculator

Here, I am providing you 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. You can also have a look at other useful articles related to Excel on our website.


Related Articles

Nehad Ulfat

Nehad Ulfat

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

Leave a reply

ExcelDemy
Logo