Find SECOND last active cell in column
Hi,
I use the Index/Match formula to find the last active cell in a column quite effectively.
I'm wondering though how to adapt it to find the second last active cell?
EX: Last active cell formula:
Data:
a 10
b 11
c 0
d 12Is it possible to use INDEX/MATCH to return the second most recent active cell, in this case, B2?Code:= INDEX(B$1:B$4,MATCH(9.99999999999999E+307,B$1:B$4)) = 12
Thanks!
Are your numbers in ascending order like you write in example?
If yes =LARGE(B:B;2) would be easiest
"Relax. What is mind? No matter. What is matter? Never mind!"
Hi Mworonuk,
You should just be able to add a -1 to the end of the match function, e.g.
=INDEX(B$1:B$4,MATCH(9.99999999999999E+307,B$1:B$4)-1)
Of course, if the cell above the last value is blank, this will return 0, not the second to last actual value.
10, 11, 0, 12 are in ascending order??? I have been out of the math loop for a long time, but really?Originally Posted by zbor
If the numbers are either 0 or incremented by 1 over the last non-zero number, you can use =INDEX(B1:B4,MATCH(MAX(B1:B4)-1,B1:B4,0)) or just =INDEX(B:B,MATCH(MAX(B:B)-1,B:B,0)) if you don't have anything else in that column. This will also work with blanks in the data.
Last edited by darkyam; 01-15-2010 at 06:08 PM.
I apologize, I doubted my own abilities!
For any one else looking for this type of formula here it is.
Note:Code:=INDEX(INDIRECT("A1:A"&(ROW(INDEX(A$1:A$4,MATCH(9.99999999999999E+307,A$1:A$4)))-1)),MATCH(9.99999999999999E+307,INDIRECT("A1:A"&(ROW(INDEX(A$1:A$4,MATCH(9.99999999999999E+307,A$1:A$4)))-1))))
- To use in a different column you must also manually change the INDIRECT text: "A1:A" to whatever you like
- This will return 0, but will not return any nil values (which is what I wanted) To eliminate the 0... well that's more complicated.
Well, since 0 is not a number, I don't see mistake in the question :p10, 11, 0, 12 are in ascending order??? I have been out of the math loop for a long time, but really?
"Relax. What is mind? No matter. What is matter? Never mind!"
Wow you guys responded faster than I imagined!
Actually the numbers could be anything in no particular order. The case is though that it would either be a positive or nill.
Case:
Reports are received for widgets every month (each row), some months are bigger than others, some months aren't reported at all.
Therefore the numbers could be:
We're never interested in non-reported months.Data:
a 99
b 105
c
d 23
What I posted seems to work, but its rather complicated because I don't fully understand the match function...
You can simplify yours considerably by usingAs with yours, it will return the 0.Code:=INDEX(A1:INDEX(A1:A4,MATCH(99^99,A1:A4)-1),MATCH(99^99,A1:INDEX(A1:A4,MATCH(99^99,A1:A4)-1)))
Here, try this:
=LOOKUP(99^99;INDIRECT("B1:"&ADDRESS(MATCH(99^99;B:B)-1;2)))
(replace ; with , if needed)
=LOOKUP(99^99,INDIRECT("B1:"&ADDRESS(MATCH(99^99,B:B)-1,2)))
"Relax. What is mind? No matter. What is matter? Never mind!"
Non-Volatile version of zbor's approach which replaces INDIRECT with darkyam's INDEX approach - again assumes non-report months are non-numeric
To handle instances of only one number you would need to add a handler - pending version you could use IFERROR (XL2007 and beyond) or use a COUNT testCode:=LOOKUP(9.99E+307,B1:INDEX(B:B,MATCH(9.99E+307,B:B)-1))
Code:=IF(COUNT(B:B)<=1,"insufficient records",existing formula)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks