Vlook

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

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.

--

"bimseun" <[email protected]> wrote in message news:[email protected]...
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

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

"bimseun" wrote:

> 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
>

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


On Sat, 27 May 2006 04:46:01 -0700, Tom <[email protected]> wrote:

>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
>
>"bimseun" wrote:
>
>> 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
>>

Gord Dibben MS Excel MVP