Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
=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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|