How to Calculate Final Grade in Excel (in 3 Steps)

Written by Mrinmoy Roy

Last updated: Jun 17, 2024

In the dataset “Student Marksheet”, we have columns showing student names and various courses. Next to each student’s name, we have a grade for each respective course.

Step 1 – Calculate Total Marks

Enter the following formula in cell F5:

=SUM(C5:E5)

Here, the range C5:E5 refers to the subject marks for the first student.

Press ENTER.

Drag the Fill Handle icon from cell F5 to F14.

You will get the total marks for all the students in the three subjects.

Step 2 – Find Out the Total Marks in Percentages

Enter the following formula in cell G5:

=F5/300

Here,

F5 is the sum of the marks for each student.
300 is the total marks in those three subjects.
Press ENTER.

Drag the Fill Handle icon from cell G5 to G14.

Sselect the range G5:G14.
Go to the Home tab in the main ribbon.
In Number, click on the Percentage command.

You will get the total marks for each of the students in percentages.

Step 3 – Calculate Final Grade

More than 80% of marks are equal to grade A.
Marks between 70% to 80% refer to grade B.
Marks between 60% to 70% refer to grade C.
Marks less than 60% refer to grade D.

Let’s convert the percentages into the final grades based on these grading criteria.

Insert the following formula in cell H5:

=IF(G5>80%,"A",IF(G5>70%,"B",IF(G5>60%,"C","D")))

Press ENTER.

Formula Breakdown

IF(G5>60%,”C”,”D”) returns grade C if the marks in percentages are in between 60% to 70%. For marks less than 60%, the IF function returns a grade of D.
IF(G5>70%,”B”) returns grade B for the range of 70% to 80%.
IF(G5>80%,”A”) returns grade A for the range of 80% marks or above.