Can this be done?

[jkclub2000]

I need a formula at cell A11 to pick up the last non-empty value of the
range (A1-A10). For example

A1 101
A2 102
A3 103
A4 104
A5 105
A6
A7
A8
A9
A10
A11 -> 105
----------

A1 101
A2 102
A3 103
A4 104
A5 105
A6 106
A7 107
A8
A9
A10
A11 -> 107

Thanks in advance for any help!

 

[JulieD]

Hi

for a full explaination of the whole subject check out
http://www.xldynamic.com/source/xld.LastValue.html

for a simple formula which assumes that you've not got text and the last
number won't be a zero
=LOOKUP(2,1/(-A1:A10<>0),A1:A10)

Cheers
JulieD

 

[Bob Phillips]

=INDEX(A1:A11,MAX(IF(LEN(A1:A11)>0,ROW(A1:A11))))

which is an array formula, so commit with Ctrl-Shift-Enter


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JulieD]

Hi Bob

changing the formula to
=INDEX(A1:A10,MAX(IF(LEN(A1:A10)>0,ROW(A1:A10))))

might suit the OP better

Cheers
JulieD

 

[Bob Phillips]

Lol. In my testing I used A1:A9 (who knows why), but as I was replying I saw
the 11, so changed it. One would hope the OP had the nous to figure that.

Regards

Bob