Excel does not have a rank function that returns sequential numbers. All Excel’s ranking functions can return duplicated ranking numbers. Here is a solution to creating a sequential ranking system with no duplicates.
I wrote about this technique a couple of years back – see link below. The formula from this post forms the basis of the final solution.
This solution had a couple of problems.
- Text caused an error.
- 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.
Here is a single formula that spills your entire result without having to copy anything down…
=LET(r,A2:A18,s,SORT(HSTACK(IF(ISNUMBER(r),r,CHAR(8)),SEQUENCE(ROWS(r))),,-1),y,SORTBY(SCAN(0,TAKE(s,,1),LAMBDA(a,x,IF(ISNUMBER(x),a+1,0))),TAKE(s,,-1)),IF(y=0,””,y))
and if you want it as a LAMBDA to put in the Name Manager…
=LAMBDA(r,LET(s,SORT(HSTACK(IF(ISNUMBER(r),r,CHAR(8)),SEQUENCE(ROWS(r))),,-1),y,SORTBY(SCAN(0,TAKE(s,,1),LAMBDA(a,x,IF(ISNUMBER(x),a+1,0))),TAKE(s,,-1)),IF(y=0,””,y)))
Note: The ‘r’ argument receives the range.
Thanks for that Rick – you really have mastered the spilling part of custom functions.