Browse Tag

Microsoft Excel

Excel Formula for CGPA Letter grade to points

I wrote this formula for excel to figure out the grade points when I insert certain letter grade .

Since I study in North South University, I’m following their grading policy.

Numerical ScoresLetter GradeGrade Points (Per Credit)
93 and aboveA Excellent4.0
90 – 92A-3.7
87 – 89B+3.3
83 – 86B Good3.0
80 – 82B-2.7
77 – 79C+2.3
73 – 76C Average2.0
70 – 72C-1.7
67 – 69D+1.3
60 – 66D Poor1.0
Below 60F* Failure0.0
 I** Incomplete0.0
 W** Withdrawal0.0
 R** Retaken0.0

Assuming the following is 3 credit course. I have to put formula in the green bordered cell D7 below.  When I put the letter ‘B’ for example it will multiply 3 (grade point) with 3(credit hours) which resulted in 9.  If it was A, the result would have been 3*4=12. 

=IF(C7=”A”,4B7,IF(C7=”A-“,3.7B7,IF(C7=”B+”,3.3B7, IF(C7=”B”,3B7, IF(C7=”B-“,2.7B7, IF(C7=”C+”,2.3B7, IF(C7=”C”,2B7, IF(C7=”C-“,1.7B7, IF(C7=”D+”,1.3B7, IF(C7=”D”,1B7,0))))))))))

These are just nested if statements. You just need to be careful of those parentheses.