Grading Help

  • Thread starter Thread starter Teacher
  • Start date Start date
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
 
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
 
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.
 
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
 
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
 
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.
 
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
 
Hi Gord
I tried your code on sunday and it was working grade. I opened the excel again today and it does change column B when I changes column A
 
Back
Top