Fairly easy one..

[leperm]

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.

 

[Chip Pearson]

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.


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


"leperm" <[email protected]>
wrote in message
news:[email protected]...

 

[leperm]

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

 

[Guest]

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

 

[Chip Pearson]

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.



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


"leperm" <[email protected]>
wrote in message
news:[email protected]...