# Assigning Numeric Values to Letter Grades (& vice versa)

Posted by Gloria Tidwell on April 26, 2001 1:40 PM

I am a college English instructor who's been called on to submit my course grades in spreadsheet form.

For ease of data entry, I would like to be able to enter the letter grades from my gradebook, and have the spreadsheet do the assignment of the numeric value so that the grades can be averaged and then the final grade converted to a letter.

First, I need to know how to do that process.

Secondly, the numeric values change from cell to cell. For instance, an "A" letter grade for a simple essay may be worth 25 points, while an "A" for a more complex work may be worth 75 points. Is there a way to distinguish these on a column by column basis?

Thank you for the help.

Posted by Mark W. on April 26, 2001 1:59 PM

So, let's say you have a column of Letter Grades
("A","B","C"...) and column of Weight values.
How is it you want to apply these weights? I
would expect that an "A" would indicate a numeric
score from 90 to 100. To which of these scores
would you want to "map" an "A"? As you can see
it's much more self-evident going from a score
to a letter grade. Going the other way requires

Posted by Kevin James on April 26, 2001 2:04 PM

Numeric Values to Letter Grades (& vice versa)

Gloria:

I'll email you an idea.

Posted by Dave Hawley on April 26, 2001 11:00 PM

Hi Gloria

The best approach for your problem would be a macro placed with the Sheet_Change event, like the one below;

To place it in right click on the sheet name tab of the sheet you will be using. Then paste in the code below:

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
'www.ozgrid.com

'assigns a numeric value to a grade.
'Dependent on column.

Dim WatchRange As Range

If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub

On Error Resume Next
Set WatchRange = Range("A1:H65536")

If Not Intersect(Target, WatchRange) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ResetMe

'Assign the grade value, depending on Column
Case "A", "a"

Select Case Target.Column 'C to D
Case 1
Target = 25
Case 2
Target = 35
Case 3
Target = 45
Case 4
Target = 55
Case Else 'E to H
Target = 65
End Select
Target.NumberFormat = """A"""

'Assign the grade value, depending on Column
Case "B", "b"

Select Case Target.Column 'C to D
Case 1
Target = 15
Case 2
Target = 25
Case 3
Target = 35
Case 4
Target = 45
Case Else 'E to H
Target = 55
End Select
Target.NumberFormat = """B"""

'Assign the grade value, depending on Column
Case "C", "c"

Select Case Target.Column 'C to D
Case 1
Target = 10
Case 2
Target = 20
Case 3
Target = 30
Case 4
Target = 40
Case Else 'E to H
Target = 50
End Select
Target.NumberFormat = """C"""

'Assign the grade value, depending on Column
Case "D", "d"

Select Case Target.Column 'C to D
Case 1
Target = 5
Case 2
Target = 10
Case 3
Target = 15
Case 4
Target = 20
Case Else 'E to H
Target = 25
End Select
Target.NumberFormat = """D"""

End If 'WatchRange intersects Target

ResetMe:
Application.EnableEvents = True
Set WatchRange = Nothing

End Sub

Now, make the needed changes to the range to monitor for A,B,C or D. Then put in the values for each grade depending on column.