Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formula not picking up correct data
I using Excel 2003 to create a "Lookup" formula to calculate grade points and
grade point averages. I want to insert the grade and have the grade converted to a grade point then multiply by the number of credits for the class. I created a vector with the grade and a corresponding vector with the grade point. The formula I am using is =LOOKUP(D6, B1:N1, B2:N2) where D6 is the lookup value (the grade); b1:n1 is the lookup vector; and b2:n2 is the result vector. The result is that I get an "#N/A" error message when I input some grades, I get the incorrect grade point when I input other grades, and correct grade points when I input other grades. (Ex. 1. an "A" gets an "#N/A" error message. Ex. 2. an "A+" is calculated at 3.67 instead of 4 as indicated in the corresponding result vector. Ex. 3. a "C" is caluclated at 2.67 instead of 2 as indicated in the corresponding result vector. Ex. 4. A "B" is calculated at 3.0 as indicated in the corresponding result vector.) What am I doing incorrectly? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formula not picking up correct data
Skeyesq --
I see a couple of problems. First, the second argument in your lookup formula should be range that has all columns, not just the reference colomn. Second, you need to have the 'false' as the fourth argument. Here's something that works for me: A B 1 A+ 4.00 2 A 3.67 3 A- 3.50 4 B+ 3.33 5 B 3.25 6 B- 3.00 7 8 A+ =VLOOKUP(A8,A1:B6,2,FALSE) HTH "skeyesq" wrote: I using Excel 2003 to create a "Lookup" formula to calculate grade points and grade point averages. I want to insert the grade and have the grade converted to a grade point then multiply by the number of credits for the class. I created a vector with the grade and a corresponding vector with the grade point. The formula I am using is =LOOKUP(D6, B1:N1, B2:N2) where D6 is the lookup value (the grade); b1:n1 is the lookup vector; and b2:n2 is the result vector. The result is that I get an "#N/A" error message when I input some grades, I get the incorrect grade point when I input other grades, and correct grade points when I input other grades. (Ex. 1. an "A" gets an "#N/A" error message. Ex. 2. an "A+" is calculated at 3.67 instead of 4 as indicated in the corresponding result vector. Ex. 3. a "C" is caluclated at 2.67 instead of 2 as indicated in the corresponding result vector. Ex. 4. A "B" is calculated at 3.0 as indicated in the corresponding result vector.) What am I doing incorrectly? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup formula not picking up correct data
Is your lookup vector sorted in ascending order as required?
Gord Dibben MS Excel MVP On Sun, 7 Feb 2010 11:27:01 -0800, skeyesq wrote: I using Excel 2003 to create a "Lookup" formula to calculate grade points and grade point averages. I want to insert the grade and have the grade converted to a grade point then multiply by the number of credits for the class. I created a vector with the grade and a corresponding vector with the grade point. The formula I am using is =LOOKUP(D6, B1:N1, B2:N2) where D6 is the lookup value (the grade); b1:n1 is the lookup vector; and b2:n2 is the result vector. The result is that I get an "#N/A" error message when I input some grades, I get the incorrect grade point when I input other grades, and correct grade points when I input other grades. (Ex. 1. an "A" gets an "#N/A" error message. Ex. 2. an "A+" is calculated at 3.67 instead of 4 as indicated in the corresponding result vector. Ex. 3. a "C" is caluclated at 2.67 instead of 2 as indicated in the corresponding result vector. Ex. 4. A "B" is calculated at 3.0 as indicated in the corresponding result vector.) What am I doing incorrectly? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to get a correct total when changing data within a formula | Excel Worksheet Functions | |||
picking correct number with < additional question | Excel Discussion (Misc queries) | |||
Picking correct number with < | Excel Discussion (Misc queries) | |||
IN AN IF FORMULA COPY STRING OF DATA IF A CELL IS CORRECT | Setting up and Configuration of Excel | |||
Picking data | Excel Worksheet Functions |