Tracking GPA

[darkwing_duck]

I want to enter my kids' grades by class (A through F, with a point
value for each grade) and have a formula calculate their GPA quarter
over quarter, year over year, and by subject. So the spreadsheet
might look like:

A B C D E F
1
2 Grade Pt. Value
3 A 4
4 Class Student 1 Student 2 B+ 3.5
5 English 9 A A B 3
6 Math 9 C B C+ 2.5
7 Music A C 2
8 Theater A D+ 1.5
9 PE A A D 1
10 History 9 B C+ F 0
11 Biology 9 C B

.... and so on ...

Where the classes are listed in column A, the students are in columns
B & C, and the grading scale is a group of cells off to the right.

Then I want to create a formula that creates a GPA based on the
numbers in the grading scale. The easiest thing to do is to not use
letter grades and just do an average (=average(B5,B6,B8,B9,B10,B11).
However, I want this to be usable by someone other than me (like my
wife and kids), so I want them to simply be able to plug in a letter
grade and see the result (note also, that one might be taking a class
that the other isn't, like the Music & Theater examples above).

TIA,
Robert

 

[nathan_savidge]

A combination of the 2 would work. Have a table with your grades and the the
points then in your average use a vlookup based on the results, so the A's
(my school report ) ) will then be entered, but the average value will be
calculated on its points i.e. 4.

let me know if you need any help.

 

[rbrown999]

A combination of the 2 would work.  Have a table with your grades and the the
points then in your average use a vlookup based on the results, so the A's
(my school report ) ) will then be entered, but the average value will be
calculated on its points i.e. 4.

let me know if you need any help.










- Show quoted text -

Thanks, but I'm not following how I would do that. I tried to create
a cell for the "9th Grade GPA" where I tried to combine the two
formulas, but failed. My formula was
"=AVERAGE(C2:C5(VLOOKUP(C2:C5,F2:G8,1,FALSE)))" where the grades the
student got was in column C and the grade/point value were in columns
F & G. I got a #REF! error. What did I do wrong?

 

[pdberger]

Your VLOOKUP formula isn't set up right. The syntax is

=VLOOKUP(item-to-check,lookup-range,column-offset)

So

A B
1 A 4
2 B 3
3 C 2
4 D 1
5 F 0
6
7 A =VLOOKUP(A7,A1:B5,2,FALSE)

Check the Excel help on this function.

 

[rbrown999]

Your VLOOKUP formula isn't set up right.  The syntax is

=VLOOKUP(item-to-check,lookup-range,column-offset)

So

              A                 B
1            A                 4
2            B                  3
3            C                  2
4            D                 1
5            F                 0
6
7           A          =VLOOKUP(A7,A1:B5,2,FALSE)

Check the Excel help on this function.






- Show quoted text -

OK, but I'm struggling to find how I would combine the "average"
function and the "vlookup" function in the same formula in your
message above (probably just me being dense). The end goal is to get
a GPA using this information.

 

[pdberger]

The easiest way would be to set up a column to compute the individual lookup
values, and then a separate cell to average them. You can make it a lot more
complex than that, but that's the simplest.

 

[rbrown999]

The easiest way would be to set up a column to compute the individual lookup
values, and then a separate cell to average them.  You can make it a lotmore
complex than that, but that's the simplest.






- Show quoted text -

OK, created separate formulas to accomplish. Thanks for the assist.