I ended up adding a column that checked if the number in the next row was
zero and the current row was not zero. If true return a 1, false return 0.
Then I used a sumproduct of the two columns.

but your example is interesting... that sure is thinking outside the box...!

"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?