#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bimseun
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Lewis Clark
 
Posts: n/a
Default Vlook

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" wrote in message ...
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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Tom
 
Posts: n/a
Default Vlook

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<=10 0),"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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Vlook

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 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<=1 00),"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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLook Up Formula potsie via OfficeKB.com Excel Discussion (Misc queries) 0 April 26th 06 01:58 AM
Subject: vlook up 1/25/2006 1:39 PM PST arcticale Excel Discussion (Misc queries) 1 January 27th 06 07:10 PM
vlook up arcticale Excel Discussion (Misc queries) 1 January 25th 06 10:36 PM
vlook up Sean Excel Worksheet Functions 1 July 14th 05 11:04 PM
How do I use drop down list selections/values in a vlook up formu. CL Excel Discussion (Misc queries) 2 January 19th 05 11:39 PM


All times are GMT +1. The time now is 09:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"