Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Conversion from a letter grade to a number grade

This is a discussion on Conversion from a letter grade to a number grade within the Excel Questions forums, part of the Question Forums category; What I am trying to do is simply enter a letter grade in one column per student, then have Excel ...

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    4

    Default Conversion from a letter grade to a number grade

    What I am trying to do is simply enter a letter grade in one column per student, then have Excel convert that letter grade into a number grade in another column.

    What function must be used and how?

    Espiritu

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,589

    Default Re: Conversion from a letter grade to a number grade

    If A1 contains uppercase A, then:

    =CODE(A1)

    returns 65 which is the ANSI value (B is 66 etc). To assign the value 1 to Grade A, you could use:

    =CODE(A1)-64

  3. #3
    New Member
    Join Date
    May 2003
    Posts
    4

    Default Re: Conversion from a letter grade to a number grade

    Thank you! This has given me food for thought and a push in the right direction.

    I'm working on figuring out how to assign the value 4 to A, 3 to B, etc.

    Joe

  4. #4
    Board Regular Atholl's Avatar
    Join Date
    May 2002
    Location
    Scotland
    Posts
    434

    Default Re: Conversion from a letter grade to a number grade

    Here's an alternative of mine, but it's from number to letter:


    Function MARK(a)

    If a >= 70 Then
    MARK = "A"
    End If

    If a >= 60 And a <= 69 Then
    MARK = "B"
    End If

    If a >= 50 And a <= 59 Then
    MARK = "C"
    End If

    If a >= 40 And a <= 49 Then
    MARK = "D"
    End If

    If a >= 35 And a <= 39 Then
    MARK = "E"
    End If

    If a >= 30 And a <= 34 Then
    MARK = "F"
    End If

    If a < 30 Then
    MARK = "G"
    End If

    If a = 0 Then
    MARK = "N/A"
    End If
    End Function


    My marking scale may differ from yours, of course!!

    Atholl
    Excel 2000
    Windows XP Pro

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default Re: Conversion from a letter grade to a number grade

    Quote Originally Posted by Espiritu108
    ...I'm working on figuring out how to assign the value 4 to A, 3 to B, etc...
    =VLOOKUP(lookup-value,{"A",4;"B",3;"C",2;"D",1},2,0)

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,589

    Default Re: Conversion from a letter grade to a number grade

    Quote Originally Posted by Espiritu108

    I'm working on figuring out how to assign the value 4 to A, 3 to B, etc.

    Joe
    If it's only A to D then:

    =69-CODE(A1)

  7. #7
    New Member
    Join Date
    May 2003
    Posts
    4

    Default Re: Conversion from a letter grade to a number grade

    Atholl,

    I've never entered anything like that in Excel. I would have to guess that you wrote VBA. Am I correct? If so, then it seems that's a good way to go. The reason being that I was just informed that the grading scale is from 60-100, and not the A=4, B=3, C=2, etc. grading scale. More like 91-100=A, 81-90=B, etc.

    I am actually researching the answer for another who has asked me for help.

    Thanks Atholl!
    ----------------------
    Aladin,

    Excellent! Looks like I'll have to inform the man of this option. Thanks!


    Espiritu

  8. #8
    New Member
    Join Date
    May 2003
    Posts
    4

    Default Re: Conversion from a letter grade to a number grade

    Thanks again, Andrew!

    Espiritu

  9. #9
    Board Regular Atholl's Avatar
    Join Date
    May 2002
    Location
    Scotland
    Posts
    434

    Default Re: Conversion from a letter grade to a number grade

    Espiritu,

    In the code, 'a' is your cell of interest. Enter =MARK(A1) in a cell in your worksheet (this assigns 'a' in the VBA code as cell A1 in the spreadsheet).
    Now, when you enter a number (from 0 - 100) in cell A1, the corresponding mark will appear in the cell where you entered =MARK(A1)
    All you have to do is play about with the code to make it suit your grading scheme.


    Hope that makes it clearer

    Atholl
    Excel 2000
    Windows XP Pro

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default Re: Conversion from a letter grade to a number grade

    Quote Originally Posted by Andrew Poulsom
    ...If it's only A to D then:

    =69-CODE(A1)
    Shouldn't you upper A1...

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com