You appear to be looking for the last non-zero value...

=LOOKUP(2,1/(ABS(E1:INDEX(E1:E10,MATCH(9.99999999999999E+307,E 1:E10)))0),E1:E10)

For a very large range, this would be expensive.

Simon Shaw wrote:
this works so long as the formula within the range is not returning a zero
for the remaining amounts...

34
25
57
67
48
0
0
0

the formula returns zero.

"JulieD" wrote:


Hi Simon

(BTW you can't have 31 nested IFs ... only 7)
however, this formula should give you what you need:
=INDEX(E1:E10,MATCH(9.99999999999999E+307,E1:E10 ))

where E1:E10 is the range of your balances.
For an explaination of how this formula works check out
http://www.xldynamic.com/source/xld.LastValue.html

Cheers
JulieD

"Simon Shaw" wrote in message
...

I have a monthly inventory sheet, it has an opening balance column, a
couple
of columns for different activity and an ending balance - each row is a
day
of the month. Untill today() = the row date (column A), the ending balance
column remains zero.

I have a total row at the bottom for the activity columns, but for the
ending balance column I would like to be able to display the last balance.
I
have thought about an IF statement to check each cell until I find a
value,
but 31 nested IF's sounds a little crazy.

Ideas?