Vlook

[bimseun]

Hi,
please I need guidance in using Vlook to output grade of students this
=>80 A
70-79 B
60-69 C
50-59 D
45-49 E
<45 F
thanks,
bimseun

 

[Lewis Clark]

Assume your grading scale was in the range A1:B6. Sort the numbers in ascending order:
0 F
45 E
50 D
....
80 A

If the overall average was in cell C1, then use:
=VLOOKUP(C1,A1:B6,2)

This looks for the value in cell C1 in the first (numbers) colum of the grade table, and returns the letter grade from the second column. If the exact value is not found, it will default to the next lowest letter grade. Ajust the ranges to fit.

--

Hi,
please I need guidance in using Vlook to output grade of students this
=>80 A
70-79 B
60-69 C
50-59 D
45-49 E
<45 F
thanks,
bimseun

 

[Tom]

Another way to do it:
=IF(AND(A1>=0,A1<=45),"F",IF(AND(A1>=45,A1<=49),"E",IF(AND(A1>=50,A1<=59),"D",IF(AND(A1>=60,A1<=69),"C",IF(AND(A1>=70,A1<=79),"B",IF(AND(A1>=80,A1<=100),"A",IF(A1>=101,"102+","grade")))))))

Paste this into the cell where you want the grade to appear, referencing the
cell where you type in the grade

Tom

 

[Gord Dibben]

And yet another way.

Assuming scores are in column A starting at A1.

In B1 enter this formula then drag/copy down column B

=LOOKUP(A1,{0,31,41,51,61,71,81,91,101},{"E","D","C-","C","C+","B","B+","A"})

Example only. Adapt for your scores and grades.

Note the curly braces internally.


Gord Dibben MS Excel MVP

Another way to do it:
=IF(AND(A1>=0,A1<=45),"F",IF(AND(A1>=45,A1<=49),"E",IF(AND(A1>=50,A1<=59),"D",IF(AND(A1>=60,A1<=69),"C",IF(AND(A1>=70,A1<=79),"B",IF(AND(A1>=80,A1<=100),"A",IF(A1>=101,"102+","grade")))))))

Paste this into the cell where you want the grade to appear, referencing the
cell where you type in the grade

Tom

Gord Dibben MS Excel MVP