=LOOKUP(99^99,Sheet1!B:B)

Entered in a cell on Sheet2


Gord Dibben MS Excel MVP

On Tue, 10 Jun 2008 15:11:05 -0700, cynomolgous
wrote:

Hi.
This is so close to what I want to do, but I can't figure out how to modify
the formula to suit my spreadsheet. Instead of showing the difference
between the starting weight and the most recent weight, I just want a
formula in one cell which will return the value from the most recent entry in
the column (the last cell that isn't blank).

So, say I have dates in column A and weights in column B. On another
worksheet I have a cell that should retun the most recent weight entered in
column B. What formula will work?

Thanks--

"Biff" wrote:

Sure....

=A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0)

The IF(COUNT(B:B) is just to make sure there is a weight entered in column
B. If there wasn't then LOOKUP(10000,B:B) would return an error.

LOOKUP(10000,B:B)

This is kind of hard to explain but I'll do it in laymans terms........

You want to find the last numeric value entered in a range. In this
particular application the numeric value is a persons weight. The way the
function works is if the lookup value (10000) is greater than the MAX value
in the lookup array (B:B) then it returns the last entry in the range that
is less than the lookup value. So, we use an arbitrary number as the lookup
value (1000) that is so large there is no possible way that an entry in the
range (B:B) will be greater than this arbitrary number.

Here's the logic:

No human being can possibly weigh 10,000 lbs. Since the weights in the range
are guaranteed to be less than 10,000 the formula returns the last numeric
value in the range.

Biff

"Kath" wrote in message
...
Thanks Biff - I don't suppose you could explain why it works?
Both your formula and Ragdyer's formula work, but I dont understand why.

Thanks

"Biff" wrote:

Hi!

Try this:

Assume starting weight is in cell A1. Periodic weights are entered in
column
B starting in B1:

=A1-IF(COUNT(B:B),LOOKUP(10000,B:B),0)

Biff

"Kath" wrote in message
...
I am creating a spreadsheet that will record weight gained/lost and I
have
the basic spreadsheet done.

However, I need to have a cell that returns a value equal to the
difference
between the starting weight and the most recent weight - which is not
necessarily the lowest weight, so I can't use MIN()

Values are being added every few days and as such Excel needs to update
as
needed each time. All weight data is entered within the same column.

Has anyone got any advice please.

Thanks.