Rafiul Hasan
If you are working for a school or any educational institution, then you may need to make a grade sheet calculator describing the grade percentage and letter grade for each of the students. Excel offers you a number of practical and suitable ways for calculating grade percentages. In this article, I will show you some tricks with proper illustrations about how to make a grade calculator in Excel.
Download Practice Workbook
You can download the practice book from the button below.
2 Suitable Ways to Make a Grade Calculator in Excel
In this section, you will find 2 suitable ways to make a grade calculator in an Excel workbook. Let’s figure it out now!
1. Making of a Simple Grade Calculator
Let’s say, we have got a dataset of percentage range and corresponding letter grades for making a grade calculator sheet for the students of a school.
We want to prepare a grade sheet on the basis of this dataset. There are two steps for calculating the grade. Firstly, you have to calculate the grade for each subject and then you have to extract the average letter grade on the basis of the grade of each subject. Now, I will demonstrate the process one by one.
1.1. Grade Calculator for Each Subject
We want to calculate the grade percentage and letter grade for each subject. You may have converted the percentage to whole number. But here, we will get the percentage from the whole number.
In order to do so, proceed with the following steps.
Steps
- First of all, create a sheet describing the subjects, total marks, and obtained marks of the corresponding subject.
- Now, apply the following formula:
=D5/C5Here,
- C5= Total Marks
- D5= Obtained Marks
- Then, select the cell> go to the Home tab> select percentage(%) from the Number group and you will get the grade percentage.
- Now, use the Fill Handle Tool to Autofill the formula to the down cells.
- As a result, you will get the grade percentage for each subject
- Here, we will apply the VLOOKUP function to get the letter grade. Apply the following formula for the first cell and the cell will return you the letter grade.
=VLOOKUP(E5,Sheet1!$C$5:$D$14,2,TRUE)Here,
- E5= Grade Percentage
- C5= First cell of the lookup array
- D14= Last cell of the lookup array
- 2= 2nd column of the lookup array which needs to be printed as the result
- TRUE= for exact match
- Now, drag the formula down and you will get the letter grade for each subject.
Read More: How to Apply Percentage Formula in Excel for Marksheet (7 Applications)
1.2. Calculate Average Grade
Now, it’s time to get the overall grade which means the average grade based on the grade obtained for each subject.
- First, we will use the AVERAGE function. Apply the following formula to get the average grade percentage.
=AVERAGE(E5:E10)Here,
- E5= the first cell for the average value
- E10= the last cell for the average value
- Now, apply the following formula to get the average letter grade.
=VLOOKUP(G5,Sheet1!C5:D14,2,TRUE)Here,
- G5= Average grade percentage
- C5= First cell of the lookup array
- D14= Last cell of the lookup array
Read More: How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)
2. Apply Nested IF to Create Grade Calculator
You can also calculate the grade using the Nested IF formula. You have the data for the percentage and the corresponding letter grade; from here, you want to calculate the letter grade for each subject. In order to demonstrate this method, follow the steps below.
Steps:
- Firstly, apply the marks obtained and get the grade percentage just like method 1.1.
- Then, apply the following formula to get the letter grade.
=IF(E5<I4,J3, IF(E5<I5,J4, IF(E5<I6,J5, IF(E5<I7,J6, IF(E5<I8,J7, IF(E5<I9,J8, IF(E5<I10,J9, IF(E5<I11,J10, IF(E5<I12,J11,J12)))))))))Formula Breakdown
The cell E5 denotes the grade percentage (78%) obtained for Physics and the cell I4 denotes the initial value (40%) of the percentage range for the letter grade D. So, if the E5<I4, then the cell will print J3 which means the letter grade F. But, if it doesn’t meet the condition, then it will look for the value until it meets the condition. When it finds that E5< I12, immediately it returns A to the cell.
- After that, drag the formula down and you will get the letter grade for the corresponding subjects.
You can calculate the average grade percentage and the letter grade by just following method 1.2.
Read More: How to Make Result Sheet in Excel (with Easy Steps)
Grade Calculator
Here, I am providing you with a grade calculator so that you can extract the letter grade from your inserted data. Input the obtained mark on the blank section and you will get the Grade Percentage, Letter Grade, Average Percentage, and Average Letter Grade.
Conclusion
In this article, I have tried to show you different ways to make a grade calculator in Excel. I hope from now on you can easily make a grade calculator in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy. Have a great day!
Related Articles
- Calculate Subject Wise Pass or Fail with Formula in Excel
- How to Make Automatic Marksheet in Excel (with Easy Steps)
- Calculate Grade Using IF function in Excel (with Easy Steps)
- Excel Formula for Pass or Fail with Color (5 Suitable Examples)
- Calculate Percentage of Marks in Excel (5 Simple Ways)
- How to Calculate Average Percentage Increase for Marks in Excel Formula
- Calculate College GPA in Excel (3 Handy Approaches)
