Can this be done?
 

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!

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

wrote in message
ups.com...
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!

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


wrote in message
ups.com...
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!

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" wrote in message
...
=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)


wrote in message
ups.com...
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!

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


"JulieD" wrote in message
...
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" wrote in message
...
=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)


wrote in message
ups.com...
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!