How do I find the last number in a column?

[dries]

If somebody could help me with this I would be very happy, I can't figure it
out:
In a sheet I have a column of figures to which new figures are added regularly.
At the side of the column I'd like to have a cell containing the last number in
the column so I can see that in one go without having to scroll to the end of
the column.
I tried all sorts of stuff but cannot get it to work, all help would be highly
appreciated!
Thanks
Dries

 

[Pete_UK]

If your numbers are in column A, then put this formula in B1 (or
somewhere not in the same column):

=LOOKUP(10^10,A:A)

Hope this helps.

Pete

 

[Matt Richardson]

If somebody could help me with this I would be very happy, I can't figure it
out:
In a sheet I have a column of figures to which new figures are added regularly.
At the side of the column I'd like to have a cell containing the last number in
the column so I can see that in one go without having to scroll to the end of
the column.
I tried all sorts of stuff but cannot get it to work, all help would be highly
appreciated!
Thanks
Dries

Lets say your column of figures is A and you want the result showing
in B1. Type the following function into B1:-

=OFFSET(A1,COUNTA(A:A)-1,0)

and that should do the trick.

Hope this helps,
Matt Richardson
http://teachr.blogspot.com

 

[Stan Brown]

In a sheet I have a column of figures to which new figures are
added regularly. At the side of the column I'd like to have a cell
containing the last number in the column so I can see that in one
go without having to scroll to the end of the column.

http://www.contextures.on.ca/xlNames01.html#Dynamic
gives the technique for a dynamic name that refers to the whole
column. You can easily modify that to a dynamic name that contains
only the last filled element in the column.

I tried all sorts of stuff but cannot get it to work, all help
would be highly appreciated!

Just once when somebody says something like this, I wish they would
tell specifically what they've tried.

 

[Harlan Grove]

Lets say your column of figures is A and you want the result showing
in B1. Type the following function into B1:-

=OFFSET(A1,COUNTA(A:A)-1,0)

and that should do the trick.

This fails when there are blank cells above or nonnumeric cells below the
last number in column A. Also, OFFSET is volatile, so it recalculate more
often than necessary. Whenever your OFFSET formula would return the correct
result, so would

=INDEX(A:A,COUNTA(A:A))

but this INDEX formula calls only nonvolatile functions. However, the LOOKUP
formulas other respondents suggested is always better because it recalcs
faster and is more robust, ALWAYS returning the last number in the column no
matter what else is in the column.