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

Gloria, I believe we need a bit more info.

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
some additional logic.

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)
'Written by OzGrid Business Applications
'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

Select Case Target 'Determin grade

'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 Select 'Target grade
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.

Push Alt+Q to return to Excel and save.

What will happen is each time you type in any one of the letters the code will change it to it's correct numeric value (depending on Column). It will then apply a custom format so that the letter you type still appears.

So in short you may type "A" or "a" in column A and it will change to 25, but still appear as "A". You can see it's real (or underlying) value by clicking in the cell and looking in the Formula bar.

Any problems or question, let me know.

Dave

OzGrid Business Applications