How to Make a Grade Calculator in Excel (2 Suitable Ways)

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.

Dataset for making a Grade Calculator in Excel

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.

Make a grade Calculator in Excel

  • Now, apply the following formula:
=D5/C5

Here,

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

Make a grade Calculator in Excel

  • 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

Make a Grade Calculator in Excel

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

Make a Grade Calculator in Excel

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

Make a Grade Calculator in Excel

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.

Make a Grade Calculator in Excel

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

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo