excel function

[Clive]

How do I get Excel to return the last number in a column
of figures which is being contunally added to? For
example, a column of figures which is updated each day to
display a current bank balance, I want to get the latest
balance into a summary sheet but as figures are added the
reference always points to the old cell and not the newly
created cell.

 

[Ron Rosenfeld]

How do I get Excel to return the last number in a column
of figures which is being contunally added to? For
example, a column of figures which is updated each day to
display a current bank balance, I want to get the latest
balance into a summary sheet but as figures are added the
reference always points to the old cell and not the newly
created cell.

If the figures are in column A, the array-entered formula:

=INDEX(A1:A65535,MAX(ISNUMBER(A1:A65535)*ROW(A1:A65535)))

will return the number that is in the last row.

To array-enter a formula, after typing or pasting it into the cell, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
cell.


--ron