Grading Help

T

Teacher

Hi I am trying to setup an excel spread for showing the
grade in a cell. for e.g
if Cell m1 has a range of 60 to 65 then cell n1 = "d-"
if Cell m1 has a range of 66 to 69 then cell n1 = "d"
if Cell m1 has a range of 70 to 74 then cell n1 = "d+"
if Cell m1 has a range of 74 to 79 then cell n1 = "c-"
if Cell m1 has a range of 79 to 84 then cell n1 = "c"
if Cell m1 has a range of 84 to 86 then cell n1 = "c+"
if Cell m1 has a range of 86 to 88 then cell n1 = "b-"
so on and so on more then seven range.
I need step by step help to write a vba code and calling
that code in n1,n2 n3 and all the way till n whatever....
Thanks in advance
 
F

Frank Kabel

Hi
no need for VBA. Try the following
1. Add a new sheet with your lookup value. Lets call this sheet
'lookup'. the desing should look like the following:
A B
1 60 D-
2 66 D
3 70 D+
......

2. Now enter the following formula in cell N1
=VLOOKUP(M1,'lookup'!$A$1:$B$20,2)
copy this formula down for all rows
 
T

Teacher

This is not what I was looking for but it works great.
Can you please explain why I don't put the range and it
still works. Does it look as range form current cell to
next cell. Thanks a lot for your help. If you can explain
this it will be great and also out of curiosity can this
be done using VBA, Since I never done any vba just wanted
to try if you can help.
Thanks Again.
 
F

Frank Kabel

Hi
The VLOOKUP formula I gave you does not look for an excat match (as I
omitted the 4th parameter) but returns the values that is equal or that
is the largets value that is smaller than the lookup value. You may
have a look at
- the Excel helpfile
- http://www.mvps.org/dmcritchie/excel/vlookup.htm

Therefore the upper boundary of your range is not required.

For your second question: Though this can be done in VBA this would
either be accomplished by calling the same worksheetfunction out of VBA
or using a select case statement (or some other possible solution). All
of them would be slower than the direct worksheet function approach.
If you want to dig into vBA you may have a look at
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
G

Greg Wilson

First, note that, except for the first four, the numeric
ranges in your post overlap. The value of 79, for
instance, would qualify for both a grade of C- and C. I
took the liberty to make my own interpretation. Change
the array referenced below to suit.

Taken from Chip Pearson's site:
http://www.cpearson.com/excel/excelF.htm#Grades

Suggested is that you create a name refering to an array
that contains the numeric values vs. letter grades. This
is very similar to creating a named range which you may be
familiar with. The steps are easy:

1) Select Insert from the main menu
2) Select Name
3) Select Define
4) In the "Names in Workbook:" box enter "Grades"
5) In the "Refers to:" box type the following. Note the
equals sign and curly brackets:
={60, "D-"; 66, "D"; 70, "D+"; 75, "C-"; 80, "C"; 87, "C+"}
6) In Cell N1 enter: "=VLookup(M1, Grades, 2)"

Cell N1 will now automatically display the letter grade
corresponding to the numeric value in Cell M1 with no need
to execute a macro or to format multiple cells. You can
increase the size of the array to include additional
grades if you like.

Regards,
Greg
 
T

Teacher

Thanks Frank, This news group is great and your response was so fast. I thank you agan for you help. I think if we need more people like you to make this world a great place

Thank you for your help agai
Friend in USA.
 
G

Gord Dibben

Teacher

Some VBA event code for your perusal.

Right-click on the sheet tab and "View Code"

Copy/paste this code into that blank module. Adjust numbers and grades to
suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim vRngInput As Variant
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: text = "F"
Case 20 To 35: text = "E"
Case 36 To 50: text = "D"
Case 50 To 65: text = "C"
Case 66 To 85: text = "B"
Case Is > 85: text = "A"
End Select
'Apply the Letter Grade
Excel.Range("B" & n).Value = text
Next rng
End Sub

As you type a number in Column A, the letter grade will appear in Column B

Gord Dibben Excel MVP
 
Top