In this tutorial, I am going to show you 3 handy approaches to calculate college GPA in excel. You can use these methods to find out the GPA of any number of courses. Also, the methods are very similar to each other which will give you the opportunity to practice a lot in Excel.
What Is GPA?
As you might already know, GPA stands for grade point average. It is the standard way to measure academic achievements in the US on a scale of 0 to 4. To get GPA, we multiply the credit hours by the grade points of a certain course. Then we sum the results of these multiplications for all the courses and then divide that by the total number of credit hours.
3 Handy Approaches to Calculate College GPA in Excel
In the dataset for this excel tutorial, we will see how to calculate the college GPAÂ of a student in 4 courses. Then in the next 3 columns, we have the letter grades obtained by the student, the respective grade points, and then the credit hours. We will determine the values of the empty cells in the following sections.
1. Using SUM Function to Calculate College GPA in Excel
The SUM function in excel is one of the most used functions and it calculates the sum of specific cells. We can use this function to easily calculate the college GPA in excel. Let us see how to do that.
Steps:
- First, double-click on the cell F5 and enter the following formula:
=D5*E5
- Now, press Enter and you should get the total points of the respective course.
- Next, copy the formula of cell F5 to the cells below using Fill Handle and you will get the remaining course points.
- Here, click on cell F9 and enter the below formula:
=SUM(F5:F8)
- Now, press Enter and you will get the total points for all courses.
- Then, double-click on cell F10 and type in the following formula:
=F9/SUM(E5:E8)
- Finally, press the Enter key and excel will calculate the GPA.
Read More: How to Make Result Sheet in Excel
2. Utilizing VLOOKUP Function
The VLOOKUP function in excel looks for a specific value in a range of cells organized vertically. If we want to bring the grade points from a second table and then calculate the college GPA in another excel sheet, we should use this function. Follow the steps below to apply this function.
Steps:
- To begin with, double-click on cell D5 and enter the below formula:
=VLOOKUP(C5,$H$5:$I$9,2,FALSE)
- Now, press Enter and this will determine the respective grade point.
- Here, drag the Fill Handle to copy the VLOOKUP formula to the remaining cells below.
- As a result, this will get the grade points for the remaining courses.
- After that, click on cell F5 and type in the formula below:
=D5*E5
- Next, press Enter to calculate the total points.
- Here, copy the formula to the cells below using the Fill Handle.
- Now, enter the formula below in cell F9.
=SUM(F5:F8)
- Then, press Enter and the SUM formula will calculate the total points.
- After that, double-click on cell F10 and insert the formula below:
=F9/SUM(E5:E8)
- Finally, press Enter and the formula will determine the GPA.
Read More: How to Make Automatic Marksheet in ExcelÂ
3. Calculate College GPA by Applying SWITCH Function in Excel
The SWITCH Function in excel compares a single value with multiple values and returns the first match found. We can use this excel function to calculate the college GPA of students without the need for a second table. See the steps below.
Steps:
- To start with, go to cell D5 and type in the formula below:
=SWITCH(MID(C5,1,1),"A",4,"B",3,"C",2,"D",1,"F",0)
- Now, press the Enter key and you should be able to see the grade point.
- Then, copy the SWITCH formula to the cells below using the Fill Handle.
- Next, double-click on cell F5 and insert the following formula:
=D5*E5
- After that, press the Enter key and copy this formula to the remaining cells below as we saw previously.
- Consequently, you will get the points for each course.
- Again, go to the cell F9 and insert the formula below:
=SUM(F5:F8)
- Here, press Enter.
- Immediately, you will see the total points inside cell F9.
- Then, double-click on cell F10 and type in the following formula:
=F9/SUM(E5:E8)
- Lastly, press Enter again and this will determine the required GPA.
Read More: How to Make a Grade Calculator in ExcelÂ
How to Compute Grades of Students in Excel
If you do not have the letter grades already, then you will need to compute them first. For that, we will use the mark sheet of 6 students as a sample dataset. Then we will use the IFS function to get the appropriate grades.
Steps:
- First, go to cell D5 and type in the below formula:
=IFS(C5>=90,"A",C5>=80,"B",C5>=70,"C",C5>=60,"D",C5<60,"F")
- Now, press the Enter key and the formula will determine the appropriate grade.
- Then, simply copy the above formula to the remaining cells below using the Fill Handle.
- As a result, you should get the grades for all of the students.
Read More: Calculate Grade Using IF function in Excel
Things to Remember
- To ensure that the VLOOKUP formula works properly, you need to organize the points in the second table in an ascending or descending order.
- The IFS function is available in MS Excel 2016 and onwards.
- If you do not have the IFS function inside your version of Excel, you can use a nested IF function.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
I hope that you really found the methods to calculate college GPA in excel to be useful. I have used here some simple datasets to make things easier. But the methods should work smoothly for more complex datasets as well. Also, if you get stuck at any step, I would suggest reviewing them a few times to see if you are missing any points. Â If you have any queries, please let me know in the comments.
Related Articles
- Calculate Subject Wise Pass or Fail with Formula in Excel
- How to Calculate Grades with Weighted Percentages in Excel
- Excel Formula for Pass or Fail with ColorÂ
- How to Calculate Percentage of Marks in ExcelÂ
