If you’re a student, there’s a good chance that you have to calculate your grades at the end of each semester. And if you’re taking a lot of courses, that can mean a lot of grades to keep track of. Excel can be a great tool for helping you keep track of your grades and calculate your final grade for the semester. In this article, I will show you how to calculate the final grade in Excel in three easy steps. So, without having any further discussion, let’s get straight into the topic.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Steps to Calculate Final Grade in Excel
I will use the following sample student mark sheet to show you calculate the final grade in Excel.
Steps-1: Calculate Total Marks
So, I’ve got a dataset of student marks in three subjects in three different columns. At first, I will calculate the total marks each student acquired. For this purpose, I will use a simple formula using the SUM function.
Now follow the steps below to calculate the total marks each student got.
❶ First of all, insert the following formula in cell F5.
=SUM(C5:E5)Here, the range C5:E5 refers to the subject-wise marks the first student got.
❷ Then press the ENTER button.
❸ After that, drag the Fill Handle icon from cell F5 to F14.
Finally, you will get the total marks for all the students in three subjects.
Read More: How to Calculate Subject Wise Pass or Fail with Formula in Excel
Step-2: Find Out Total Marks in Percentages
After calculating the total marks each student got, we need to convert the total marks into percentages. For 3 subjects, the total mark is 300. Thus, by dividing the total marks each student got by 300, we will get the marks in fractions. Finally, converting those fraction marks into percentages is our target.
Now follow the steps below:
❶ Insert the following formula in cell G5.
=F5/300Here,
- F5 is the sum of the marks each student got.
- 300 is the total marks in those three subjects.
❷ Now press the ENTER button.
❸ Drag the Fill Handle icon from cell G5 to G14.
❹ Now select the range G5:G14.
❺ After that, go to the Home tab in the main ribbon.
❻ In the Number group, click on the Percentage command.
Finally, you will get the total marks each of the students got in percentages.
Read More: How to Apply Percentage Formula in Excel for Marksheet
Step-3: Calculate Final Grade
Now we will set conditions to convert the marks in percentages into the final grades.
For this instance, let’s consider the following cases:
- More than 80% of marks equal to grade A.
- Marks between 70% to 80% refer to grade B.
- Marks between 60% to 70% refer to grade C.
- And the marks less than 60% refer to grade D.
Based on these grading criteria, let’s convert the percentages into the final grades.
For that,
❶ Insert the following formula in cell H5.
=IF(G5>80%,"A",IF(G5>70%,"B",IF(G5>60%,"C","D")))❷ Then press the ENTER button.
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.
❸ Now drag the Fill Handle icon from cell H5 to H14.
Finally, you will get the final grades calculated for each of the students in the range H5:H14.
Read More: Calculate Grade Using IF function in Excel
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.
Conclusion
To sum up, we have discussed steps to calculate the final grade in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.
