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!

Try this, assuming your table is in Range(A1: I10) and row 1 is your headers.
In B10

Please Login or Register  to view this content.

Drag/Fill Right to I10

N.B. you can delete any column that isn't required

Is that what you mean? See the attached.

I took a somewhat different approach to Marcol's suggestion.

EDIT: See Post#8

With this solution I've created a little lookup table in M2:N6 . With Column M representing the Letter Grades and Column N representing the "points" ... i..e. M2 = A , N2 = 4

ADVANTAGE:
The nice things about using a sinlge lookup table for the grades is ...
1) that if you change the grades values you only have to do it in one place and NOT have to update formula
2) if the parents/students want to see what each grade is worth they only need to look at the table.


"Current Formula" (i.e. Column E)

=IF(B2>0,VLOOKUP(B2,$M$2:$N$6,2,FALSE) *2,0) + IF(C2>0,VLOOKUP(C2,$M$2:$N$6,2,FALSE) *2,0) + IF(D2>0,VLOOKUP(D2,$M$2:$N$6,2,FALSE),0)


Final Grade Formula (i.e. Column I )

=IF(F2>0,VLOOKUP(F2,$M$2:$N$6,2,FALSE) *2,0) + IF(G2>0,VLOOKUP(G2,$M$2:$N$6,2,FALSE) *2,0) + IF(H2>0,VLOOKUP(H2,$M$2:$N$6,2,FALSE),0) +E2

EDIT: See Post #8

Must be having an off day - here are simplified versions:

In E2

Please Login or Register  to view this content.

And in I2

Please Login or Register  to view this content.

I would suggest you put Data Validation in place (using A,B,C,D,E as a list) to ensure correct data is entered.