Find Penultimate Non-Blank Cell in Column

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.

Perhaps using this ARRAY formula?

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

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

bump to appear..post#5

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.