Home 
Search 
Today's Posts 
#11




Is D8 formatted to Percentage?
Entering 45 if not formatted to % will always return A because 45 is much higher than .85 Gord Dibben Excel MVP On Sun, 3 Apr 2005 11:07:02 0700, Neoseraph wrote: "Neoseraph" wrote: Sorry, I tried to open a new workbook and it still doesn't change anything, this is what the formula looks like with a cell: =VLOOKUP(D8,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"}) and this is what the formula looks like with a new value: =VLOOKUP(45,{0.00,0.50,0.65,0.70,0.85},{"F","D","C ","B","A"}) "Peo Sjoblom" wrote: If you get a REF error with that formula you must have either deleted something linked to A1 or used a non existent reference. Try the formula in a new workbook and put in a value in A1  Regards, Peo Sjoblom "Neoseraph" wrote in message ... Ok, when I tried your method, it gives me #REF! either by entering a number in A1 or the cell A1 itself, please help me solve the problem... "Gord Dibben" wrote: Another method of grading as you desire. Enter this formula in B1. You don't need a separate table, the table is in the formula. =LOOKUP(A1,{0,50,65,70,85},{"F","D","C","B","A"}) Enter a number in A1 Gord Dibben Excel MVP On Sun, 3 Apr 2005 08:15:03 0700, Neoseraph wrote: Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D818. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? 
#12




Make your 'tablearray' references absolute. Instead of
=VLOOKUP(D8,I9:J13,2), use =VLOOKUP(D8,$I$9:$J$13,2). Then when you drag the formula, you'll still be reference the proper lookup table. "Neoseraph" wrote: Hmmm, yeah, now that gave me "A" for grades that are higher then 0.85 but then when I try to drag the formula down into the other cells, they all gave me "A" too even when the grade is 5% (which is suppose to be "F"), so what's wrong? "Fred Smith" wrote: VLOOKUP assumes your table is in ascending order, not descending. Change your table to: 0.00 F 0.50 D 0.65 C 0.70 B 0.85 A And it should work.  Regards, Fred Please reply to newsgroup, not email "Neoseraph" wrote in message ... Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D818. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? 
#13




Set your Grade Table up as follows
0 F 50% D 65% C 70% B 85% A Do Insert Name Define, and name this range GRADES Then, assuming your GradePercentage is in A1, use this formula to look up the grade =VLOOKUP(A1,GRADES,2,TRUE) Vaya con Dios, Chuck, CABGx3 "Neoseraph" wrote in message ... Hi, I need help to setup the VLOOKUP function for an assignment. Everytime I try to do it, it says, #NA. I need to use VLOOKUP to put in the letter grade for a list of students's grades in % on their midterms. The average % is in cells D818. The lookup table I made (in cells I9:J13) is as followed: 85% and above A 70% and above B 65% and above C 50% and above D Below 50% F This is what I tried for VLOOKUP but got #N/A: lookup value: D8 table array: I9;J13 col_index_num: 2 so the final formula looks like this: =VLOOKUP(D8,I9:J13,2) and that gave me #N/A. Can anybody help please??? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Simplify Vlookup function in Excel  Excel Worksheet Functions  
Vlookup w/Date Function  Excel Worksheet Functions  
I want to use Vlookup function and AND function in a single formu.  Excel Worksheet Functions  
carrying a hyper link when using the vlookup function  Excel Worksheet Functions  
carrying a hyper link when using the vlookup function  Excel Worksheet Functions 