Making letters equal number values for semester and exam grades

Hey,

First time posting here and I have an issue I'm hoping someone can help me with. I work with some students who are on the edge of failing classes and I need help creating an excel file that I can use to enter in letter values and obtain their number of "Quality points" that go into passing a class. I set up the document like this

________Q1 - Q2 -Midterm - Current - Q3 -Q4-Final Exam -Final
Class 1
class 2
class 3

With it going down to a total of 8 classes. Class 1 is row 2 and class 8 is row 9. Q1 is column "B" with Final being column "I", so I have Q1 values being B2:B9, Q2 being C2:C9, etc.. Currently I have a 2 simple SUM fuctions per row to add up QI,Q2, and midterm under Current and Current, Q3, Q4, and Final Exam under Final. I'm just filling in the number values but I want to move to letter values being entered instead.

and to complicate things, Q1,Q2,Q3,and Q4 are worth double points (A=8,B=6,C=4,D=2,E=0) while the midterm and final are worth normal GPA values (A=4,B=3,C=2,D=1,E=0).

I was hoping someone could help me with the right functions to be able to enter letter values instead of number values and come out with number values under "current" and "final". It makes it much easier to show to students and staff who dont know the value system.

If I left anything out, let me know and I'll post more. Thanks for the help in advance!