Categories

## 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 Scores Letter Grade Grade Points (Per Credit) 93 and above A Excellent 4.0 90 – 92 A- 3.7 87 – 89 B+ 3.3 83 – 86 B Good 3.0 80 – 82 B- 2.7 77 – 79 C+ 2.3 73 – 76 C Average 2.0 70 – 72 C- 1.7 67 – 69 D+ 1.3 60 – 66 D Poor 1.0 Below 60 F* Failure 0.0 I** Incomplete 0.0 W** Withdrawal 0.0 R** Retaken 0.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.