Find Penultimate Non-Blank Cell in Column

[Jennasis]

Hi,

I have been trying to find a way of finding the penultimate non-blank cell in a column and I have hit a brick wall. The current formula I have does not work if there are two or more identical values in the column, as it is a MAX (or LARGE) formula, so I need to try something else.

The blank cells contain formulae, so a better description would perhaps be the last non-numerical cell.

The current non-working formula is:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[zbor]

upload example workbook

[Jennasis]

upload example workbook

Unfortunately I am unable to do so, due to the company firewall here - however, I can demonstrate here a little to give an example:

Column With data
125
26
-Formua with blank result-
26
-Formua with blank result-
978
-Formua with blank result-
978
-Formua with blank result-
56

I would want to pick up the bottom 978 result from this column

[zbor]

For last non numerical value try:

Please Login or Register  to view this content.

[Fotis1991]

Perhaps using this ARRAY formula?

=INDEX(A1:A20,COUNTA(A1:A20)-1)

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[Fotis1991]

bump to appear..post#5

[Jennasis]

Thanks for your suggestions! :-)

I finally found a method that seems to work ok

Please Login or Register  to view this content.

then I added a fraction to the formula result in each cell, via another column, to make the numbers unique

Please Login or Register  to view this content.