VLookup Tables

[Guest]

I am a teacher and trying to make my grades electronic instead of manual. I
have the number grades calculated however; I wanted excel to automatically
translate the numeric grade into a letter grade. For example if the numeric
grade is a 90 than in the next column I want excel to put an A- in the field.
I tried to do that with creating a separate worksheet for the VLOOKUP that
lists the numeric grade associated with the letter grade but I don't
understand how to do it.

 

[Aqib Rizvi]

The table could be

50 C+
60 B
70 B+
80 A
90 A+

while the formula would be =VLOOKUP(A7,A1:B5,2)

Regards
AQIB RIZVI

 

[Guest]

Try this:

C1: 0 D1: E
C2: 60 D2: D
C3: 70 D3: C
C4: 80 D4: B
C5: 90 C5: A

Ensure that you have c sorted in increasing order

For your lookup, use this (a1 is the numeric value)

=VLOOKUP(A1,$C$1:$D$5,2,TRUE)

 

[Ron Coderre]

See if this gets you most of the way there....

With
A1: (a test score....eg 92)
B1:
=LOOKUP(A1,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

That formula in segments is this:
=LOOKUP(A1,{0,60,63,67,70,73,77,80,83,87,90,93,97},
{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

or...using a lookup table:
E1:E13 contains these values:
0
60
63
67
70
73
77
80
83
87
90
93
97

F1:F13 contains these grades:
F
D-
D
D+
C-
C
C+
B-
B
B+
A-
A
A+

and...
B1: =VLOOKUP(A1,$E$1:$F$13,2,1)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)