Truly unique ranking (while ignoring empty cells)

Question

I am using the following formulae for a truly unique ranking of values: How to Rank Duplicate Values Sequentially.

(As you may or may not be aware the other option (see ##) can produce erroneous results!)

However, there is a problem: I would like to ignore empty cells! Currently, empty cells are counted as having the value zero.

How do I need to change the formulae in 1 to ignore empty cells and to return no value at all? Is that even possible with an array formula?

I avoid VBA as I need to keep this dynamic.

Thank you in advance for any hints!

pascal

(##): =RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1

Solution

End result:

Method (A1 is top left):

Data2: =IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$1))))

Sorted: =SMALL($B$2:$B$8,ROW()-ROW($C$1))

Rank: =IFERROR(MATCH($B2,$C$2:$C$8,0),"")