In this tutorial, we will demonstrate 3 approaches to calculate college GPA in Excel.
What Is GPA?
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 calculate GPA, we multiply the Credit Hours by the Grade Points of a certain course. Then we sum the results of the product for all the courses, and divide the result by the total number of Credit Hours.
3 Handy Approaches to Calculate College GPA in Excel
To illustrate our methods, we’ll use the following dataset containing the college GPA of a student in 4 courses, along with the letter grades obtained by the student, the respective Grade Points, and the Credit Hours. We will determine the values of the Points column.
Method 1 – Using the SUM Function to Calculate College GPA in Excel
The SUM function calculates the sum of specific cells.
Steps:
- Double-click on cell F5 and enter the following formula:
=D5*E5
- Press Enter to return the Total Points for the course MATH 101.
- Copy the formula to the cells below using the Fill Handle.
- Click on cell F9 and enter the following formula:
=SUM(F5:F8)
- Press Enter to return the Total Points for all courses.
- Double-click on cell F10 and enter the following formula:
=F9/SUM(E5:E8)
- Press Enter to calculate the GPA.
Read More: How to Make Result Sheet in Excel
Method 2 – Using the VLOOKUP Function
The VLOOKUP function in Excel looks for a specific value in a range of cells organized vertically. We’ll use this function to fetch the Grade Points from a second table and calculate the college GPA in a different Excel sheet
Steps:
- Double-click on cell D5 and enter the following formula:
=VLOOKUP(C5,$H$5:$I$9,2,FALSE)
- Press Enter to return the Grade Point for MATH 101.
- Drag the Fill Handle to copy the VLOOKUP formula to the cells below.
The grade points for the remaining courses are returned.
- Click on cell F5 and enter the formula below:
=D5*E5
- Press Enter to calculate the Total Points.
- Copy the formula to the cells below using the Fill Handle.
- Enter the formula below in cell F9:
=SUM(F5:F8)
- Press Enter and the SUM formula will calculate the Total Points.
- Double-click on cell F10 and enter the formula below:
=F9/SUM(E5:E8)
- Press Enter and the formula will determine the GPA.
Read More: How to Make Automatic Marksheet in Excel
Method 3 – Using the SWITCH Function
The SWITCH Function compares a single value with multiple values and returns the first match found. We can use this function to calculate the college GPA without the need for a second table.
Steps:
- Go to cell D5 and enter the formula below:
=SWITCH(MID(C5,1,1),"A",4,"B",3,"C",2,"D",1,"F",0)
- Press Enter to return the Grade Point for MATH 101.
- Copy the SWITCH formula to the cells below using the Fill Handle.
- Double-click on cell F5 and enter the following formula:
=D5*E5
- Press Enter and copy the formula to the remaining cells using the Fill Handle.
The Points for each course are returned.
- Go to cell F9 and enter the formula below:
=SUM(F5:F8)
- Press Enter to return the Total Points.
- Double-click on cell F10 and enter the following formula:
=F9/SUM(E5:E8)
- Press Enter to determine the 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, they will need to be calculated first. We will use the mark sheet of 6 students below as a sample dataset, and use the IFS function to calculate the appropriate Grades.
Steps:
- Go to cell D5 and enter the following formula:
=IFS(C5>=90,"A",C5>=80,"B",C5>=70,"C",C5>=60,"D",C5<60,"F")
- Press Enter to return the grade for William.
- Copy the formula to the cells below using the Fill Handle.
The grades for all of the students are returned.
Read More: Calculate Grade Using IF function in Excel
Things to Remember
- To ensure that the VLOOKUP formula works properly, sort the Points in the second table in either ascending or descending order.
- The IFS function is available in MS Excel 2016 and onwards. For earlier versions, use a nested IF function.
Download Practice Workbook
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
