Thanks:  0
Likes:  0

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

5. ## Re: Conversion from a letter grade to a number grade

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. ## Re: Conversion from a letter grade to a number grade

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

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

Espiritu

8. ## Re: Conversion from a letter grade to a number grade

Thanks again, Andrew!

Espiritu

9. ## 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

10. ## Re: Conversion from a letter grade to a number grade

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

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

## User Tag List

#### Posting Permissions

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