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