Hi Biff,

I get a #REF error when I use the formula...

I think the LARGE formula requires another parameter, but not sure what to
use...

Thanks

"Biff" wrote:

Hi!

No need for helper columns.

Try this formula entered as an array with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(A1:A8,LARGE(IF(A1:A80,ROW(A1:A8)),1))

Biff

-----Original Message-----
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
news:4B5FCE43-D508-4B4E-A66E-
...
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?



.