Fairly easy one..

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

leperm - 08 Jun 2006 01:06 GMT

I want to define an array which returns the last cell in a given colum
that contains a numerical value.

Having a similar formula for the text versoin would also be useful.

.

Reply to this Message

Chip Pearson - 08 Jun 2006 01:24 GMT

The following array formula will return the last numeric value in
the range A1:A100.

=INDIRECT("A"&MAX(ROW(A1:A100)*ISNUMBER(A1:A100)))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {} in the formula
bar.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

> I want to define an array which returns the last cell in a
> given column
[quoted text clipped - 4 lines]
>
> .

Reply to this Message

leperm - 08 Jun 2006 01:39 GMT

Sorry, I actually want to return the last cell not the value in th
cell.

I.e Searches column B for the last cell with a number in it and return
the locatoin. e.g. B

Reply to this Message

Chip Pearson - 08 Jun 2006 01:45 GMT

The following array formula will return the address of the last
numeric value in A1:A10.

=ADDRESS(MAX(ROW(A1:A10)*ISNUMBER(A1:A10)),1)

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {} in the formula
bar.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

> Sorry, I actually want to return the last cell not the value in
> the
[quoted text clipped - 3 lines]
> returns
> the locatoin. e.g. B9

Reply to this Message

Gary''s Student - 08 Jun 2006 01:44 GMT

=IF(ISBLANK($A$65536),LOOKUP(2,1/($A$1:$A$65535>0),$A$1:$A$65535),$A$65536)

will return the value of the last cell in column A, number or text.

Signature

Gary''s Student

> I want to define an array which returns the last cell in a given column
> that contains a numerical value.
>
> Having a similar formula for the text versoin would also be useful.
>
> ..

Reply to this Message