Return the penultimate (second to last) non-blank value in column

Sam

New member
Joined
Apr 3, 2014
Messages
36
Reaction score
0
Points
0
Excel Version(s)
2007
Hi All,

I am using Excel 2007 for Windows.

I have a table that consists of many columns and expanding rows; for this reason, I have referenced the table as a dynamic named range called “Data”. “Data” can contain duplicate values in both columns and rows. I have numeric labels in row 3, spanning the width of my table; “Data” starts in column “G”, row 4. Column “A” contains sequential numeric values (references) that can be used as relative row numbers for my table. The cells within the table are populated with a formula that returns either a numeric value or empty text (“”) showing a blank cell. Zero is a valid numeric value in the table.

Scenario:
Find the penultimate (second to last) non-blank numeric value in a column within “Data”, and return to a cell. Please use the named range “Data” to create formula.


Sample Layout of table "Data":
Book6.xlsx attached


Expected Results:
Looking for penultimate value in column “V” in table –
Found in column “V”, row 14; return value = 0

Looking for penultimate value in column “AA” in table –
Found in column “AA”, row 11; return value = 4

Looking for penultimate value in column “AB” in table –
Found in column “AB”, row 4; return value = 0

Looking for penultimate value in column “AC” in table –
Found in column “AC”, no cell with value; return value = nothing, empty text (“”)

Looking for penultimate value in column “AD” in table –
Found in column “AD”, no cell with value; return value = nothing, empty text (“”)

Hope you can help.

Thanks,
Sam
 

Attachments

  • Book6.xlsx
    10.8 KB · Views: 159

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Assuming you enter the column of interest in, say AG2 (e.g. 16 for column "V"), then try:

=INDEX(INDEX($G$4:$AD$18,0,AG2),LARGE(IF(INDEX($G$4:$AD$18,0,AG2)<>"",ROW($G$4:$AD$18)-ROW($G$4)+1),2))

confirmed with CTRL+SHIFT+ENTER not just ENTER.
 

Sam

New member
Joined
Apr 3, 2014
Messages
36
Reaction score
0
Points
0
Excel Version(s)
2007
Thank you very much for your help and time. The formula works great! Brilliant!

Sam
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
You're welcome.

Note: You should surround with IFERROR() for when there is no "next number"

=IFERROR(INDEX(INDEX($G$4:$AD$18,0,AG2),LARGE(IF(INDEX($G$4:$AD$18,0,AG2)=AH2,ROW($G$4:$AD$18)-ROW($G$4)+1),1)),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER.
 

Sam

New member
Joined
Apr 3, 2014
Messages
36
Reaction score
0
Points
0
Excel Version(s)
2007
Will do; thank you,

You're welcome.

Note: You should surround with IFERROR() for when there is no "next number"

=IFERROR(INDEX(INDEX($G$4:$AD$18,0,AG2),LARGE(IF(INDEX($G$4:$AD$18,0,AG2)=AH2,ROW($G$4:$AD$18)-ROW($G$4)+1),1)),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER.

Sam
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I also commented on your other recent query, fyi. Let me know how that works too.
 

Sam

New member
Joined
Apr 3, 2014
Messages
36
Reaction score
0
Points
0
Excel Version(s)
2007
Just to say: yesterday -Thurs 8 May, updated Thread "Return the last values below / after a Specific Value".

I also commented on your other recent query, fyi. Let me know how that works too.

Thank you,
Sam
 

mrmbrown

New member
Joined
Aug 31, 2014
Messages
1
Reaction score
0
Points
0
This looks like exactly what I'm after. Is it possible to tweak this to handle cells containing #N/A values? ie. if you replace the blank cells in the spreadsheet with =NA() this doesn't seem to work.

Any ideas would be much appreciated!
 
Top