Nehad Ulfat
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.
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/D5Here,
- 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 and drag it down to Autofill the formula and release the mouse button.
- Hence, 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 & 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.
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
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
- How to Calculate Percentage in Excel Based on Cell Color (4 Methods)
- Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)
- How to Calculate Percentage Change with Negative Numbers in Excel
- Excel Formula for Pass or Fail with Color (5 Suitable Examples)
- How to Apply Percentage Formula in Excel for Marksheet (7 Applications)
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
- How do you Calculate Percentage Increase or Decrease in Excel
- Percentage Difference between Two Percentages Excel (2 Easy Ways)
- How to Calculate Final Grade in Excel (with Easy Steps)
- Calculate Grades with Weighted Percentages in Excel
- How to Make a Grade Calculator in Excel (2 Suitable Ways)
- Calculate Average Percentage in Excel [Free Template+Calculator]
- How to Calculate Average Percentage Increase for Marks in Excel Formula
