find the last figure within a range ???

[walshy]

Hi wonder if you could please help me...

I am trying to find a formula which finds me the last
value in a range i.e

1%
2%
3%
#n/a
#n/a
#n/a
formula = ????? (The 1 2 3% & #n/a's are all formulas
also...)

the last value would be 3%... I would much appreciate it
if you could help me... Thanks

 

[Jon Peltier]

This works for a limited range of A1:A20

{=INDEX(A1:A20,MAX(ISNUMBER(A1:A20)*ROW(A1:A20)))}

This is an array function, denoted by the curly brackets. Don't type
them, hold CTRL+SHIFT while pressing Enter. If done correctly, Excel
rewards you by placing the brackets on the formula.

It would be easier if you could use A:A, but an array formula does not
allow the use of an entire column.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______