Lookup; Grading a class

[Guest]

I am a teacher new to microsoft excel and i am grading a class
here are the marks
0-19 will equal 9
20-35 will equal 8
36-45 will equal 7
46-52 will equal 6
53-58 will equal 5
56-75 will equal 4
76-84 will equal 3
85-100 will equal 1
what do i type

I need this reply urgent
Thank you

 

[Frank Kabel]

Hi
try the following:
1. in column A put the lower boundary of your range:
A1: 0
A2: 20
....

2. In column B put the associated value
B1: 9
B2: 8
....

Now if you put a value in C1 (e.g. 12) use the following
formula in D1:
=VLOOKUP(C1,A1:B20,2,1)

 

[Gord Dibben]

Corey
Frank's VLOOKUP will do the trick but......you could automate it further.

If you are up to using some code behind your worksheet this will enter the
marks in Column B as you enter the scores in Column A.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim vRngInput As Variant
On Error GoTo ws_exit:
Application.EnableEvents = False
Set vRngInput = Intersect(Target, Range("A:A"))
n = Target.Row
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the range
Select Case rng.Value
Case Is < 20: Value = 9
Case 20 To 35: Value = 8
Case 36 To 45: Value = 7
Case 46 To 52: Value = 6
Case 53 To 58: Value = 5
Case 56 To 75: Value = 4
Case 76 To 84: Value = 3
Case 85 To 100: Value = 1
End Select
'Apply the Grade
Excel.Range("B" & n).Value = Value
Next rng
ws_exit:
Application.EnableEvents = True
End Sub

David McRitchie has instructions and info on Event code at his web-site.

http://www.mvps.org/dmcritchie/excel/event.htm


Gord Dibben Excel MVP

 

[Dave Peterson]

I think I'd move this line:
Application.EnableEvents = False
after this line:
If vRngInput Is Nothing Then Exit Sub

then .enableevents won't be kept turned off if you're outside the range.

And if you could change a bunch of cells at once, I'd make a couple of other
changes:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim n As Long
Dim vRngInput As Range
Dim myValue As Variant

Set vRngInput = Intersect(Target, Me.Range("A:A"))
If vRngInput Is Nothing Then Exit Sub

On Error GoTo ws_exit:

Application.EnableEvents = False
For Each rng In vRngInput
If IsError(rng.Value) Then
'do nothing
Else
n = rng.Row
'Determine the range
myValue = ""
Select Case rng.Value
Case Is < 20: myValue = 9
Case 20 To 35: myValue = 8
Case 36 To 45: myValue = 7
Case 46 To 52: myValue = 6
Case 53 To 58: myValue = 5
Case 56 To 75: myValue = 4
Case 76 To 84: myValue = 3
Case 85 To 100: myValue = 1
End Select
'Apply the Grade
Me.Range("B" & n).Value = myValue
End If
Next rng
ws_exit:
Application.EnableEvents = True
End Sub

(I don't like to use variables that look like reserved words (Value).

 

[Dana DeLouis]

Is there a chance of non-integers? The "Average" function, or something
similar, might return 84.00001

Dana

 

[Gord Dibben]

Thanks for the alterations Dave.

If I live long enough I may get the hang of it.

Gord