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