I am trying to create a weight loss spreadsheet. Column B has dates in
ascending order, and column c has current day's weight. I want to display
the weight corresponding with the max date that has a weight value greater
than zero.

As weight values are entered, I want the field to automatically update.

On Feb 1, the 192 should show. On Feb 2, the 191 should show. On Feb 3,
the 190 should show. On Feb 4, whatever the user inserts should show.

Spreadsheet Looks like this:

Column B Column C
02/01/2008 192
02/02/2008 191
02/03/2008 190
02/04/2008 <blank value
02/05/2008 <blank value

Try this in the cell where you want the last weight entered to show up:
=INDEX(C:C,MATCH(2000,C:C,1))
The 2000 is the max value to lookup, I figure 2000 probably exceeds any
living human's weight.
Someone may come up with a better solution, but I think this one will work
for you.

When no weights have been entered, it will show #N/A. You can get around
that with this:
=IF(ISNA(INDEX(C:C,MATCH(1000,C:C,1))),"",INDEX(C: C,MATCH(1000,C:C,1)))

"Paperback Writer" wrote:

I am trying to create a weight loss spreadsheet. Column B has dates in
ascending order, and column c has current day's weight. I want to display
the weight corresponding with the max date that has a weight value greater
than zero.

As weight values are entered, I want the field to automatically update.

On Feb 1, the 192 should show. On Feb 2, the 191 should show. On Feb 3,
the 190 should show. On Feb 4, whatever the user inserts should show.

Spreadsheet Looks like this:

Column B Column C
02/01/2008 192
02/02/2008 191
02/03/2008 190
02/04/2008 <blank value
02/05/2008 <blank value

Another one:

=IF(COUNT(C:C),LOOKUP(1E100,C:C),"")

--
Biff
Microsoft Excel MVP


"Paperback Writer" wrote in
message ...
I am trying to create a weight loss spreadsheet. Column B has dates in
ascending order, and column c has current day's weight. I want to display
the weight corresponding with the max date that has a weight value greater
than zero.

As weight values are entered, I want the field to automatically update.

On Feb 1, the 192 should show. On Feb 2, the 191 should show. On Feb 3,
the 190 should show. On Feb 4, whatever the user inserts should show.

Spreadsheet Looks like this:

Column B Column C
02/01/2008 192
02/02/2008 191
02/03/2008 190
02/04/2008 <blank value
02/05/2008 <blank value

These work!

BUT! Now, I need a very similar solution. Let's say that Column C has
zeroes in it. How to return the last value that isn't a zero? In the
example below, it will show 190. The solutions posted thus far would all
show 0. -- Thanks!!!!

Column B Column C
02/01/2008 192
02/02/2008 191
02/03/2008 190
02/04/2008 0
02/05/2008 0

Assuming that column C contains *only* numbers:

=IF(COUNTIF(C:C,"0"),LOOKUP(2,1/(C2:C200),C2:C20),"")


--
Biff
Microsoft Excel MVP


"Paperback Writer" wrote in
message ...
These work!

BUT! Now, I need a very similar solution. Let's say that Column C has
zeroes in it. How to return the last value that isn't a zero? In the
example below, it will show 190. The solutions posted thus far would all
show 0. -- Thanks!!!!

Column B Column C
02/01/2008 192
02/02/2008 191
02/03/2008 190
02/04/2008 0
02/05/2008 0