How do I find the last number in a column?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dries - 01 Feb 2008 11:23 GMT

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

Reply to this Message

Pete_UK - 01 Feb 2008 11:40 GMT

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

> If somebody could help me with this I would be very happy, I can't figure it
> out:
[quoted text clipped - 6 lines]
> Thanks
> Dries

Reply to this Message

Matt Richardson - 01 Feb 2008 11:42 GMT

> If somebody could help me with this I would be very happy, I can't figure it
> out:
[quoted text clipped - 6 lines]
> 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

Reply to this Message

Harlan Grove - 02 Feb 2008 09:13 GMT

"Matt Richardson" <mattyboy150279@gmail.com> wrote...
...

>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.

Reply to this Message

Stan Brown - 02 Feb 2008 06:22 GMT

01 Feb 2008 11:23:40 GMT from dries <dries@bessels.nospam.org>:

> 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.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Reply to this Message