[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

VLOOKUP Excel 2010

Hi,
I need your help to complete the VLOOKUP formula for my sister who grading her students.  I need to assign letter grades base upon points.

The points for the course is 600

To earn an "A", the student must have 540 or more points.
To earn a "B", the student must have 480 - 539 points.
To earn a "C", the student must have 420 - 479 points.
To earn a "D", the student must have 360 - 419 points.
Less than 360 points, earns an "F".

Student      Total Points      Grade
Mary              550       
Sue              479       
Paulo              200       
Rena              420       
Stanford              366       

            
Grade Table      
            

0
perla1962
Asked:
perla1962
  • 3
  • 3
  • 3
  • +2
2 Solutions
 
MINDSUPERBCommented:
Try this:

=IF(E2<360,"F",IF(AND(E2>=360,E2<=419),"D",IF(AND(E2>=420,E2<=479),"C",IF(AND(E2>=480,E2<=539),"B","A"))))

Ed
0
 
MINDSUPERBCommented:
The formula above assumes that the Total Points in column E.

Sincerely,
Ed
0
 
perla1962Author Commented:
I'm trying to used the VLOOKUP FUNCTION.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
perla1962Author Commented:
I've requested that this question be deleted for the following reason:

No possible solutions.
0
 
MINDSUPERBCommented:
With the data you provided, VLOOKUP function is not possible. That is the reason behind I suggested the IF function for it is the appropriate function to be used in your scenario.

Sincerely,
Ed
0
 
dlmilleCommented:
Why would you delete this question, so easily?  When you post a question in E-E, its a good idea to let the question sit for a while while E-E experts provide possible solutions.

There most certainly is a possible solution using VLOOKUP.  The form of VLOOKUP to use is:

=Vlookup(score, table, TRUE) <- score being a score from 0-600, table having an appropriate vlookup table that captures the minimum, each inflection, and the maximum for each possible result, F, D, C, B, and A.

The table looks like this:

Score      Grade
0      F
359      F
360      D
419      D
420      C
479      C
480      B
539      B
540      A


See attached worksheet, with this vlookup command on the sample data, and all the results are correct.  In addition, to prove that this solution is correct, I created 601 scores - from 0 to 600, with the vlookup result.  As you can see, the results are 100% correct.

Please accept this solution as the correct one, rather than deleting the question.

See attached.

Cheers!

Dave
gradingPapers-r1.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

first of all, "this cannot be done" may be a valid answer and is no reason to delete the question.

Second, it CAN be done with a vlookup formula. Either create a lookup table, for example starting in G1

0      E
360      D
420      C
480      B
540      A

Then the vlookup in cell C2 would be

=VLOOKUP(B2,$G$1:$H$5,2,1)

If you don't want to create a lookup table, you can embed it in the formula, see column D in the attached spreadsheet

=VLOOKUP(B2,{0,"E";360,"D";420,"C";480,"B";540,"A"},2,1)

cheers, teylyn
Book1.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Dave, why the duplication in the lookup table? If the last parameter is TRUE, then the next smallest matching result will be returned.
0
 
dlmilleCommented:
You of course are correct.  Not sure what I was thinking.  I thought I built the table as you had but probably one less on each inflection - re: 359, 419, 479, 539 and found I needed the next point up, not giving it another thought afterwards, versus pulling my initial values out.  Must have been I was seeing a bit red on this one given the nature of the "quick closure" lol.

While mine's "technically" correct, yours is more efficient and elegant.  Its been a while since I used a vlookup to grade something - used to do it all the time, re: finding quartile,deciles,etc... lol

Cooler heads prevailed, and yours is much cooler :)

Dave
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Yeah, well, can't get much more chilled than at the end of a nice, sunny Sunday with the kids tucked in bed ... :-)
0
 
dlmilleCommented:
heheheh - got that right.
0
 
perla1962Author Commented:
Thank you very much, I got it.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now