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

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 :o) ) 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.

" wrote:

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

On Apr 15, 8:18*am, nathan_savidge
wrote:
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 :o) ) 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.



" wrote:
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- Hide quoted text -

- 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?

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.


" wrote:

On Apr 15, 8:18 am, nathan_savidge
wrote:
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 :o) ) 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.



" wrote:
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- Hide quoted text -

- 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?

On Apr 15, 9:45*am, pdberger
wrote:
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.



" wrote:
On Apr 15, 8:18 am, nathan_savidge
wrote:
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 :o) ) 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.

" wrote:
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- Hide quoted text -

- 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?- Hide quoted text -

- 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.

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.

" wrote:

On Apr 15, 9:45 am, pdberger
wrote:
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.



" wrote:
On Apr 15, 8:18 am, nathan_savidge
wrote:
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 :o) ) 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.

" wrote:
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- Hide quoted text -

- 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?- Hide quoted text -

- 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.