Conversion from a letter grade to a number grade

[Espiritu108]

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

 

[Andrew Poulsom]

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

 

[Espiritu108]

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

 

[Atholl]

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

 

[Aladin Akyurek]

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

 

[Andrew Poulsom]

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)

 

[Espiritu108]

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

 

[Espiritu108]

Thanks again, Andrew!

Espiritu

 

[Atholl]

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

 

[Aladin Akyurek]

...If it's only A to D then:

=69-CODE(A1)

Shouldn't you upper A1...