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/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 => 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.
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
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
- How to Calculate Win-Loss Percentage in Excel
- How to Calculate SLA Percentage in Excel
- How to Calculate Error Percentage in Excel
- How to Calculate Mean Percentage Error in Excel
- How to Calculate Remaining Shelf Life Percentage in Excel
- How to Calculate Percentage of Completion in Excel
- How to Calculate Percentage of Budget Spent in Excel
- How to Calculate Utilization Percentage in Excel
- How to Calculate Absenteeism Percentage in Excel
- How to Calculate Savings Percentage in Excel
- How to Calculate Productivity Percentage in Excel
- How to Calculate Variance Percentage in Excel
- How to Calculate Accuracy Percentage in Excel
