Need Help With Formula

[Monte Sliger]

I hope someone can help me with this problem.

I have a worksheet (Sheet1) with the following column headings:
A B C
Date Description Amount


All data in this worksheet is arranged in date order.

I need to put a formula in a cell in another worksheet (Sheet2) which will
return the last value in column C where the corresponding data in column B
does not equal "Adjustment". For example my last five lines of data in
Sheet1 might consist of:

A B C
Date Description Amount
7/21/04 Sale -50.00
7/31/04 Adjustment 0.00
8/15/04 Purchase 25.00
8/31/04 Adjustment 1.00
9/30/04 Adjustment -1.00

I would need the formula to return the value 25.00 no matter how many
"Adjustment" lines there were either before or after the last line of data
in the list which did NOT have "Adjustment" in the description field.


Thanks for any help.
Monte Sliger

 

[Frank Kabel]

Hi
try
=LOOKUP(2,1/((B1:B1000<>"Adjustment")*(B1:B1000<>"")),C1:C1000)

 

[Monte Sliger]

Frank,

That formula works like a charm. For my own understanding, please help me
understand what the formula is doing. I can see that this is a LOOKUP
formula based on a vector syntax. I do not understand what the "1/" in
front of the lookup vector accomplishes. Also the help instructions for the
LOOKUP formula based on a vector syntax says that the values in the lookup
vector must be arranged in ascending order while the values in column B are
not arranged in any kind of order.

Any help you can give me in understanding why this formula works as it does
is greatly appreciated.

Thanks again for the formula.
Monte Sliger

 

[Frank Kabel]

Hi
the interesting part is the second parameter of LOOKUP:
1/((B1:B1000<>"Adjustment")*(B1:B1000<>""))

depending on the values in column B this returns either the value
1/(TRUE*TRUE)
1/(TRUE*FALSE)
1/(FALSE*TRUE)
1/(FALSE*FALSE)

If you use boolean values in a mathematical operation Excel coerce them
to real numbers (TRUE=1, FALSE=0). So the above would be converted to
1/(1*1)=1
1/(0*1)=#DIV/0
1/(1*0)=#DIV/0
1/(0*0)=#DIV/0

So the 2nd parameter retusn an array of '1' and #DIV/0 errors. As
LOOKUP now searches for the value 2 it returns the last element which
is smaller or equal (and that is the last '1' in this range)

 

[Monte Sliger]

Frank,

Amazing! I now see how it works, although I still do not see why the help
instructions say the values in the lookup vector must be arranged in
ascending order while the data in column B is not in any kind of order.


Anyway, thanks a lot.
Monte Sliger

 

[Frank Kabel]

Hi
this formula uses the trick that it searches for a value (2) whioch is
larger than all other values in the lookup_vector (largest value: 1)

 

[Monte Sliger]

Frank,

Thanks again for all your help.


Monte Sliger