An SGPA (Semester Grade Point Average) and CGPA (Cumulative Grade Point Average) Calculator in Google Sheets helps calculate a student’s SGPA and CGPA using grades and subject credits. The calculator then uses these inputs to compute the SGPA and CGPA by multiplying each subject’s grade points by its credits, summing these, and dividing by the total credits.
Creating an SGPA and CGPA Calculator in Google Sheets:
Follow the below steps to create an SGPA and CGPA Calculator in Google Sheets:
1. Input Column Headings for Semester Data and Calculations
Start at your desired spreadsheet cell; merge the two rows and six columns for the “Semester 1” heading. On the next row, type the headings into the cells of that row: “S. No”, “Subject Code”, “Subject Name”, “Credits”, “Grade”, “Grade Points”, and “Credit Points”.
2. Enter Your semester Information in Designated Columns
Start entering your semester details row by row below the heading, including the serial number, subject code under the “Subject Code”, the name of each subject under “Subject Name”, and the number of credits for the subject under “Credits”. You can enter your grades directly under the “Grade”, or we can use a dropdown list.
3. Create a dropdown menu for grades
- Select the cell under “Grade”.
- Click on Insert on the Menu and select Dropdown.
- Now, the Data validation panel opens.
- Under “Criteria”, select Dropdown on the Data validation rules panel.
- Select the Grades of your regulation; for extra values, click “Add another item”.
- We can then click and drag this formula down to each remaining cell in the column.
- Now, you can change the grades from the dropdown list for every subject.
4. Calculate Grade Points for Each Subject
Now, we need to assign the number of points received for each subject based on the letter grade by using the following logic:
- A+ = 10 points
- A = 9 points
- B = 8 points
- C = 7 points
- D = 6 points
- E = 5 points
- F = 0 points
- AB= 0 points
Note: For grades and grade points, check your academic regulation rules.
We can type either of the following formulas into the cell under “Grade Points” to do so:
=IF(F44 = “A+”, 10, IF(F44 = “A”, 9, IF(F44 = “B”, 8,IF(F44 = “C”, 7, IF(F44 = “D”, 6, IF(F44 = “E”, 5,0))))) )
Note: Here, F44 is the cell of the Grade.
=SWITCH(F45, “A+”, 10, “A”, 9, “B”, 8, “C”, 7, “D”, 6, “E”, 5, “F”, 0, “AB”, 0, “Completed”, 0)
Note: Here, F45 is the cell of the Grade.
Now, click and drag this formula down to each remaining cell in the column.
5. Calculate Each Subject’s Credit Points by Multiplying Credits and Grade Points
To get the Credit points for the subject, we have to multiply the subject credits and grade points.
=E44*G44
Note: Here, E44 is the cell that contains the subject credits, and G44 is the cell that contains the subject grade points.
Now, click and drag this formula down to each remaining cell in the column.
6. Total Your Credits and Credit Points at the List’s End
Sum all the subject credits by using this formula:
=SUM(E44:E53)
Note: E44:E53 is the range of all the subject credits.
Sum all the credit points by using this SUM formula:
=SUM(H44:H53)
Note: H44:H53 is the range of all the credit points.
7. Compute Your SGPA
SGPA can be obtained by dividing the sum of Credit Points by the sum of Credits.
=round(SUM(H44:H53)/SUM(E44:E53),2)
This formula divides the sum of Credit Points by the sum of Credits and rounds it to 2 decimal points.
Repeat the above steps for all the other semesters to get the SGPA for the remaining semesters.
Just copy and paste all these cells.
CGPA Calculator
Now, to calculate CGPA, start with your desired spreadsheet cell. In the column, type the headings Semesters, Credits, Credit Points, and SGPA.
In the “semesters” row, type 1 to 8 in each cell of that row.
Enter the credits of the semesters under the respective semester column. You can manually enter the credits, or you can use the = function
=E20
Note: E20 is the total credits cell of semester 1.
Enter the credit points for the semesters under the respective semester columns. You can manually enter the credit points, or you can use the = function
=H20
Note: H20 is total credit points cell of semester 1.
Enter the SGPA of the semesters under the respective semester columns. You can manually enter the SGPA, or you can use the = function
=E21
Note: E21 is SGPA cell of semester 1.
To calculate backlogs, type “backlogs” below the SGPA column.
To get the backlogs, we have to count the number of subjects that failed and were absent. Use this formula to count the backlogs.
=COUNTIF(F10:F19, “F”) + COUNTIF(F10:F19, “AB”)
Note: F10:F19 is the range of Grades of semester 1.
Backlogs Counting
Use this formula to get the backlogs of all the semesters. Change the range of that particular semester grades.
CGPA Calculation
To calculate CGPA, we have to divide the sum of the credit points of all semesters by the sum of the credits of all semesters.
Use the below formula:
=round(SUM(C146:J146)/SUM(C145:J145),2)
Percentage Calculation
To calculate the percentage, we have to use the conversion formula provided in the academic regulation manual, which is specific to our university. As per my university and regulations, I have to subtract 0.75 from my CGPA and multiply it by 10.
Use the below formula:
=(C151-0.75)*10
Class awarded
To determine our passing class, we have to see the class table in the academic regulation manual, which is specific to our university.
The formula below is as per my regulation; change the rules as per your regulation. You can use either of the formulas.
=IF(C151 >= 7.75, “First Class Distinction”, IF(C151 >= 6.75, “First Class”, IF(C151 >= 5.75, “Second Class”, IF(C151 >= 5, “Pass”, “Fail”))))
or
=SWITCH(TRUE, C151 >= 7.75, “First Class Distinction”, C151 >= 6.75, “First Class”, C151 >= 5.75, “Second Class”, C151 >= 5, “Pass”, “Fail”)
Conclusion
I hope this post helps you to create a SGPA and CGPA calculator in Google Sheets by yourself. You can use borders and color formatting for visual experience. You can also subscribe to my YouTube channel.
Thanks for reading, Have a great day.
