Re: lookup formula

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

In <f8fkt6$gpq$1@xxxxxxxxxxxxxxxxx>, 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

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

Relevant Pages

  • Re: lookup formula
    ... Hmm. ... I seem to have gotten it to work by correcting one tiny thing ... we want to take the last price from B and find the ... Now we divide that by $16.26 from B3, ...
    (microsoft.public.excel.misc)
  • Re: Help needed- Newbie access user
    ... This author may have received products and services mentioned ... rows in access by dividing them. ... ID Width Height Price ... I am supposed to divide the (price next ...
    (microsoft.public.access.reports)
  • Re: Why doesnt MSFT buy Apple?
    ... anyone tried to buy that much stock it would raise the price. ... Corporation and their 100 shares.. ...
    (comp.sys.mac.advocacy)
  • Re: [LotR] Two more Hobbit films scheduled
    ... If you're talking share price anyhow. ... about correcting the books due to the costs of building new factories, ... RE: Destroying Tomb Kings... ... Craig Little - A piece of slate inside the army book and a vigorous ...
    (rec.games.miniatures.warhammer)
  • Help needed to write UDF to calculate value based on 8 conditions
    ... To accommodate the various net cost options, I am using 16 columns to input ... Column H = Max. persons for Cost I ' max number of persons for who price ... Column Q = Max persons Unit I = pax number of persons for who the unit price ... max number of persons in, divide the unit price from by number of ...
    (microsoft.public.excel.programming)