Sequential Rank Custom Function for Excel

I wrote about this technique a couple of years back – see link below.

https://a4accounting.com.au/excel-ranking-with-sequential-numbers/

The formula from this post forms the basis of the final solution.

This solution had a couple of problems.

1. Text caused an error.

2. Zero and blank cells caused a duplication.

You can see the issues in the image below.

The formula in cell C2 has been copied down.

=RANK.EQ(A2,$A$2:$A$18)+COUNTIF($A$2:A2,A2)-1

Note the COUNTIF range expands as it is copied down.

Rows 7 and 10 return the same rank.

Row 13 has an error due to text.

A custom function can get around these issues.

Custom Function

The LAMBDA function to test is.

=LAMBDA(rng,amt,IF(ISNUMBER(amt),RANK.EQ(amt,rng)+COUNTIF(INDEX(rng,1):amt,amt)-1,””))($A$2:$A$18,A2)

This formula returns a blank cell for blank cells and text entries.

The IF and ISNUMBER functions combine to ensure only numbers are ranked.

The INDEX function is used in the COUNTIF function to anchor the first cell in the expanding range.

The range name definition is shown below.

The custom function is action is shown below. The formula in cell D2 has been copied down.