# Making letters equal number values for semester and exam grades

1. ## 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!

2. ## Re: Making letters equal number values for semester and exam grades

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.

3. ## Re: Making letters equal number values for semester and exam grades

That's awesome, thanks!, but what I was looking to do was add up the Q1, Q2 and Midterm under each individual class and get the sum to be under Current. Then add Current, Q3, Q4, and Final Exam and have the sum be under Final. The goal to get get a final point value for each class under final.

Here's what I managed to do before coming here. In this file I just put the number value associated with the letter grade but the kids I'm working with won't understand it the way I do.

4. ## Re: Making letters equal number values for semester and exam grades

I tried modifying that function you gave me to apply to cells BX:DX of each line but I have been unable to get it right. It just does the half points for each line:

=IF(LEFT(A1,1)="Q",SUM(COUNTIF(B2:D2,"A")*8,COUNTIF(B2:D2,"B")*6,COUNTIF(B2:D2,"C")*4,COUNTIF(B2:D2,"D")*2,COUNTIF(B2:D2,"E")*0),SUM(COUNTIF(B2:D2,"A")*8,COUNTIF(B2:D2,"B")*6,COUNTIF(B2:D2,"C")*4,COUNTIF(B2:D2,"D")*2,COUNTIF(B2:D2,"E")*0)/2)
I know why but nothing I do to the first half seems to work. It'll either count only column D if I remove B and C from the second half or all 3 but as half. I wasn't any good at programming when i took it 10 years ago.

5. ## Re: Making letters equal number values for semester and exam grades

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

EDIT: See Post#8

6. ## Re: Making letters equal number values for semester and exam grades

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

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

7. ## Re: Making letters equal number values for semester and exam grades

EDIT: See Post #8

8. ## Re: Making letters equal number values for semester and exam grades

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.

9. ## re: Making letters equal number values for semester and exam grades

thanks everyone! I appreciate the help. This is going to make a lot of students, teachers, and parents life easier!

I'm using it to help students at risk of failing figure out what they need to do to pass.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1