Friday, December 4, 2009

Using Nested Formulas to Calculate a GPA with Excel

I received an email request from a former colleague this week and am posting part of the email with her permission.


"I love your textbook!  I think we'll be using it for our BSN informatics course!!

On another note - I'm pulling my hair out on making a GPA spreadsheet like we used at ___!  I'm going gray over this.  Could you help me out please?  I'll be eternally grateful. :-)"

What she is asking is how to create formulas in Excel to calculate a GPA. Although the formulas may initially look intimidating, they are fairly straightforward. The formulas use If/Then statements. The formulas are based upon the A, B, C, D, F grading scale, where A is worth 4, B is worth 3, C is worth 2, D is worth 1, and F is worth 0 quality points.

My answer is noted below. I will also attach a spreadsheet with the formulas. Note that the formulas below are nested meaning that each one includes several arguments, that is, one for each grade.



A
B
C
D
E
F
1
Course
Name
Credits
Grade
Quality PTs
QP/Credits
2
ENG
College Composition I
3




=IF(D2="A",C2,IF(D2="B",C2,IF(D2="C",C2,IF(D2="D",C2,IF(D2="F",C2," ")))))

The formula above in Cell E2 is for quality points.  What it says is that if D2 (Grade) is "A", E2 (Quality Points) is the same value as C2 (Credits) - same for "B", "C", "D", and "F".
 =IF(D2="A",4*E2,IF(D2="B",3*E2,IF(D2="C",2*E2,IF(D2="D",1*E2,IF(D2="F","0","")))))

The formula above in Cell F2 is if D2 (Grade) is "A", multiply 4 times E2 (Quality Points),  - same for "B", "C", "D", and "F" except "B" = 3, "C" = 2, "D" = 1, and "F" =0.

The letter grades are in parenthesis to tell Excel that the letter is a text field.

To download a copy of the spreadsheet, go to http://hercules.gcsu.edu/jsewell/textbook/GPA_spreadsheet.xls

14 comments :

  1. Thank you for posting this!

    ReplyDelete
  2. btw i'm in 6th grade advance program!

    ReplyDelete
  3. first you need to create a mini lookup table:

    You can do this by typing a, b,c,d, f in cells A1-A5, and 4, 3, 2, 1, 0 in B1-B5on the second sheet in the workbook. Or somewhere else on you main sheet (make sure you change the formula to reflect where the table is).
    On your original worksheet place your letter grades in a column (i used column B) next to those (for me in column C) use the formula =VLOOKUP(B1,Sheet2!A1:B5,2) Where the B1 refers to the letter and the Sheet!A1:B5 refers to the location of the minichart. Please Note you cannot copy and paste this like normal formulas b/c it will change the location of the lookup.

    now in column D type the number of credits you received. (most college courses are 3)
    in column E multiply the values of column C x column D

    To get a GPA for each class in column F use the formula =E1/D1
    To get a GPA for all there are 2 options
    1: below column F (where you have already divided by 4) use the =AVERAGE(range of cells)
    2: just use =AVERAGE(range of cells)/4

    I hope that made some kind of sense.
    You can calculate your GPA through this free online GPA Calculator : https://calculator-online.net/cgpa-calculator/

    ReplyDelete
  4. A lot of companies are now improving their services to provide the best to the students like www.cheapessaywritingservices.com/faq-on-how-to-buy-essays-cheap so that they can share their knowledge with others and the company gets the best results.

    ReplyDelete
  5. These nested formulas are a source for https://www.mbaassignmenthelp.org/about-our-mba-assignment-writing-help/ to help the students because all you need is the right way in the right decision procedures.

    ReplyDelete
  6. These are the formulas when I click here which helped me to create and solve the problems for my students and I am happy to create those formulas for all of my students.

    ReplyDelete
  7. The formulas in excel have made our work so easy and we can do our work with this page on the right time without that much effort.

    ReplyDelete
  8. I have command over the formulas and can put those formulas very easily but the best ideas here is to negotiate what we can do more to help our students.

    ReplyDelete