Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup formula
I'm trying to do an HLOOKUP or something like it but am having
trouble. Here is a table from my sheet: A B C D E F G 1 Symbol Last Price Stagger % $40 $30 $20 $10 ====== ========== ========= === === === === 2 ASTI $8.30 0.60 3 METH $16.26 4.92% 0.80 4 SOHU $30.08 1.20 0.90 Basically, I've put all the data in but want the formula for Column C. For Column C, we want to take the last price from B and find the next value above it in D1:G1, then look up the corresponding value in D:G on the current row. Then we take what we found and divide it by the last price. I did it for B3 successfully. Here is the formula I used: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,3,F ALSE)/$B3 I don't know if that formula's right or wrong, but the mathematical result is right. That is, $16.26 from B2 is under $20, so we're looking up values in Column F. The value we find is 80 cents in F3. Now we divide that by $16.26 from B3, and we get 4.92%. However, when I drag my formula to C2 or C4 I get error messages. I'm not sure why. The ROUNDUP stuff seems to work, so far as I can see. So the problem's got to be in my HLOOKUP formula. (The dollar signs are just formatting; the numbers are numbers, also in the header column.) There are certainly going to be other approaches to solving this, as well. I'm looking forward to seeing some! Regards, Dallman Ross |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup formula
In , Dallman Ross <dman@localhost. spake thusly:
Hmm. I seem to have gotten it to work by correcting one tiny thing in my formula. I had =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,3,F ALSE)/$B3 .................................................. ............^ I changed that to: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,ROW (),FALSE)/$B3 That solves my immediate problem, but I still would be interested in other (simpler?) approaches. Dallman ================================================== I'm trying to do an HLOOKUP or something like it but am having trouble. Here is a table from my sheet: A B C D E F G 1 Symbol Last Price Stagger % $40 $30 $20 $10 ====== ========== ========= === === === === 2 ASTI $8.30 0.60 3 METH $16.26 4.92% 0.80 4 SOHU $30.08 1.20 0.90 Basically, I've put all the data in but want the formula for Column C. For Column C, we want to take the last price from B and find the next value above it in D1:G1, then look up the corresponding value in D:G on the current row. Then we take what we found and divide it by the last price. I did it for B3 successfully. Here is the formula I used: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,3,F ALSE)/$B3 I don't know if that formula's right or wrong, but the mathematical result is right. That is, $16.26 from B2 is under $20, so we're looking up values in Column F. The value we find is 80 cents in F3. Now we divide that by $16.26 from B3, and we get 4.92%. However, when I drag my formula to C2 or C4 I get error messages. I'm not sure why. The ROUNDUP stuff seems to work, so far as I can see. So the problem's got to be in my HLOOKUP formula. (The dollar signs are just formatting; the numbers are numbers, also in the header column.) There are certainly going to be other approaches to solving this, as well. I'm looking forward to seeing some! Regards, Dallman Ross |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup formula
In , Dallman Ross <dman@localhost.
spake thusly: I changed that to: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,ROW (),FALSE)/$B3 Still not right. If B3 is, e.g., $9.49, the lookup ends up being in the $20s column; I want it to be in the $10s column (G below). ============================================ In , Dallman Ross <dman@localhost. spake thusly: Hmm. I seem to have gotten it to work by correcting one tiny thing in my formula. I had =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,3,F ALSE)/$B3 .................................................. ...........^ I changed that to: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,ROW (),FALSE)/$B3 That solves my immediate problem, but I still would be interested in other (simpler?) approaches. Dallman ================================================== I'm trying to do an HLOOKUP or something like it but am having trouble. Here is a table from my sheet: A B C D E F G 1 Symbol Last Price Stagger % $40 $30 $20 $10 ====== ========== ========= === === === === 2 ASTI $8.30 0.60 3 METH $16.26 4.92% 0.80 4 SOHU $30.08 1.20 0.90 Basically, I've put all the data in but want the formula for Column C. For Column C, we want to take the last price from B and find the next value above it in D1:G1, then look up the corresponding value in D:G on the current row. Then we take what we found and divide it by the last price. I did it for B3 successfully. Here is the formula I used: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,3,F ALSE)/$B3 I don't know if that formula's right or wrong, but the mathematical result is right. That is, $16.26 from B2 is under $20, so we're looking up values in Column F. The value we find is 80 cents in F3. Now we divide that by $16.26 from B3, and we get 4.92%. However, when I drag my formula to C2 or C4 I get error messages. I'm not sure why. The ROUNDUP stuff seems to work, so far as I can see. So the problem's got to be in my HLOOKUP formula. (The dollar signs are just formatting; the numbers are numbers, also in the header column.) There are certainly going to be other approaches to solving this, as well. I'm looking forward to seeing some! Regards, Dallman Ross |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup formula
In , Dallman Ross <dman@localhost.
spake thusly: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,ROW (),FALSE)/$B3 That solves my immediate problem, but I still would be interested in other (simpler?) approaches. Hmm. Thinking out loud, here, but this seems to be better: =HLOOKUP(10-MOD(B3,10)+B3,$E$1:$H3,ROW(),FALSE)/$B3 Dallman |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup formula
Try:
=OFFSET(G3, 0, -INT(B3/10))/B3 Drag the formula down. As implied by your approach, the above assumes that the Last Price values cannot exceed $40. If they can, then advise on how you want to treat this. Greg "Dallman Ross" wrote: I'm trying to do an HLOOKUP or something like it but am having trouble. Here is a table from my sheet: A B C D E F G 1 Symbol Last Price Stagger % $40 $30 $20 $10 ====== ========== ========= === === === === 2 ASTI $8.30 0.60 3 METH $16.26 4.92% 0.80 4 SOHU $30.08 1.20 0.90 Basically, I've put all the data in but want the formula for Column C. For Column C, we want to take the last price from B and find the next value above it in D1:G1, then look up the corresponding value in D:G on the current row. Then we take what we found and divide it by the last price. I did it for B3 successfully. Here is the formula I used: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,3,F ALSE)/$B3 I don't know if that formula's right or wrong, but the mathematical result is right. That is, $16.26 from B2 is under $20, so we're looking up values in Column F. The value we find is 80 cents in F3. Now we divide that by $16.26 from B3, and we get 4.92%. However, when I drag my formula to C2 or C4 I get error messages. I'm not sure why. The ROUNDUP stuff seems to work, so far as I can see. So the problem's got to be in my HLOOKUP formula. (The dollar signs are just formatting; the numbers are numbers, also in the header column.) There are certainly going to be other approaches to solving this, as well. I'm looking forward to seeing some! Regards, Dallman Ross |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup formula
Forgot to meantion that you should format the Stagger % column as Percent
through Format Cells Number (if you haven't done so already). Greg "Dallman Ross" wrote: I'm trying to do an HLOOKUP or something like it but am having trouble. Here is a table from my sheet: A B C D E F G 1 Symbol Last Price Stagger % $40 $30 $20 $10 ====== ========== ========= === === === === 2 ASTI $8.30 0.60 3 METH $16.26 4.92% 0.80 4 SOHU $30.08 1.20 0.90 Basically, I've put all the data in but want the formula for Column C. For Column C, we want to take the last price from B and find the next value above it in D1:G1, then look up the corresponding value in D:G on the current row. Then we take what we found and divide it by the last price. I did it for B3 successfully. Here is the formula I used: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,3,F ALSE)/$B3 I don't know if that formula's right or wrong, but the mathematical result is right. That is, $16.26 from B2 is under $20, so we're looking up values in Column F. The value we find is 80 cents in F3. Now we divide that by $16.26 from B3, and we get 4.92%. However, when I drag my formula to C2 or C4 I get error messages. I'm not sure why. The ROUNDUP stuff seems to work, so far as I can see. So the problem's got to be in my HLOOKUP formula. (The dollar signs are just formatting; the numbers are numbers, also in the header column.) There are certainly going to be other approaches to solving this, as well. I'm looking forward to seeing some! Regards, Dallman Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula | Excel Worksheet Functions | |||
Lookup formula help | Excel Worksheet Functions | |||
lookup formula? | Excel Worksheet Functions | |||
Help with Lookup/Sum Formula | Excel Discussion (Misc queries) | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |