lookup formula

[Dallman Ross]

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,FALSE)/$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

 

[Dallman Ross]

In <[email protected]>, 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,FALSE)/$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

==================================================

 

[Dallman Ross]

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).

============================================

 

[Dallman Ross]

=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

 

[Greg Wilson]

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

 

[Greg Wilson]

Forgot to meantion that you should format the Stagger % column as Percent
through Format > Cells > Number (if you haven't done so already).

Greg