How to rank values without ties
✓ Verified in LibreOffice 25.8.7.3Give every row a distinct rank even when values are equal — no shared 2nd place.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =RANK(B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1 | Fill down. Earlier rows win ties; add a second criterion to COUNTIF to break ties differently. |
| Google Sheets | =RANK(B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1 | Identical. |
| LibreOffice Calc | =RANK(B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1 | Identical. |
How it works
Plain RANK gives both 20s rank 2 and skips rank 3. Adding COUNTIF with the expanding range counts how many times the value has appeared so far: the first 20 gets 2+1−1=2, the second gets 2+2−1=3. Every rank is now distinct, with earlier rows winning ties — which makes the ranks safe to use as INDEX/MATCH keys for building sorted leaderboards in versions without SORT/SORTBY.
Verified, not just documented
We ran =RANK(B4,$B$2:$B$6)+COUNTIF($B$2:B4,B4)-1 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. Every formula here is confirmed by actually executing it.