Statistical
Rank Without Ties formula: Unique Sequential Ranks (RANK.EQ + COUNTIF)
Assign unique sequential positions to five numeric scores, resolving equal scores by their source-row order.
Resolve equal scores by source-row order
RANK.EQ deliberately gives equal scores the same rank. That is appropriate for a tied leaderboard. If a downstream lookup instead needs a unique position for every row, add the number of earlier occurrences of the current score.
The first occurrence wins each tie. This is an explicit row-order policy, not a judgment that one tied result is better. Reordering the source rows can change those positions. Use a stable secondary key if ties must be resolved independently of row order.
This five-row example requires five numeric scores. It rejects blanks and numeric text; zero and negative scores are valid. The expanding COUNTIF starts at the first score and ends at the current row.
| Player (Col A) | Score (Col B) | Standard RANK.EQ | Tie-Broken Unique Rank |
|---|---|---|---|
| Ava (Row 2) | 92 | 1 | 1 + 1 - 1 = 1 |
| Ben (Row 3) | 85 | 3 | 3 + 1 - 1 = 3 |
| Cara (Row 4) | 92 (Tie!) | 1 (Duplicate!) | 1 + 2 - 1 = 2 (fills slot #2!) |
| Dan (Row 5) | 78 | 5 | 5 + 1 - 1 = 5 |
| Eva (Row 6) | 85 (Tie!) | 3 (Duplicate!) | 3 + 2 - 1 = 4 (fills slot #4!) |
Descending Unique Rank Without Ties (Highest = 1)
Enter this formula in C2 and fill down to C6. The score range stays fixed; the current score and end of the COUNTIF range advance with each row. Highest score receives position 1.
=IF(COUNT($B$2:$B$6)<>5,"Enter five numeric scores",RANK.EQ(B2,$B$2:$B$6,0)+COUNTIF($B$2:B2,B2)-1)Ascending Unique Rank Without Ties (Lowest Time = 1)
Enter this formula in D2 and fill down to D6. Lowest score receives position 1, with ties still resolved in source-row order.
=IF(COUNT($B$2:$B$6)<>5,"Enter five numeric scores",RANK.EQ(B2,$B$2:$B$6,1)+COUNTIF($B$2:B2,B2)-1)Try the example
Use the original values or change them to test your understanding.