+ Reply to Thread
Results 1 to 9 of 9

Making letters equal number values for semester and exam grades

  1. #1
    Registered User
    Join Date
    03-10-2011
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    4

    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!
    Last edited by Pfisch87; 03-10-2011 at 07:42 PM. Reason: solved

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

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

    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.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    03-10-2011
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    4

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

  4. #4
    Registered User
    Join Date
    03-10-2011
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    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. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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
    Last edited by Cutter; 03-10-2011 at 07:41 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    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

    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
    Attached Files Attached Files
    Last edited by nimrod; 03-10-2011 at 06:06 PM.

  7. #7
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

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

    EDIT: See Post #8
    Last edited by Cutter; 03-10-2011 at 07:42 PM.

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    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.
    Last edited by Cutter; 03-10-2011 at 07:50 PM.

  9. #9
    Registered User
    Join Date
    03-10-2011
    Location
    Maryland
    MS-Off Ver
    Excel 2003
    Posts
    4

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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