If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|