How to Rank Values Without Ties in Excel?
Tired of tied ranks in your data? Discover how to rank values uniquely from highest to lowest using a simple formula.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Rank values in a list from highest to lowest without repeating rank numbers for tied values"
=RANK.EQ(A1, $A$1:$A$10, 0) + COUNTIFS($A$1:$A$10, A1, $A$1:A1, This formula ranks values in a list from highest to lowest while ensuring that tied values receive unique ranks.
Step-by-step breakdown
- RANK.EQ function determines the rank of a number in a list.
- The COUNTIFS function counts how many times the current value appears in the list up to the current row.
- The formula adjusts the rank by subtracting the count of tied values to ensure unique ranks.
- The final result is a unique rank for each value, even if some values are tied.
Edge cases & warnings
- All values are the same, resulting in a unique rank of 1 for all.
- Only one value in the list, which should return a rank of 1.
- Negative values included, which should still rank correctly.
- Empty cells in the range that should be ignored in ranking.
Examples
"Values: 10, 20, 20, 30"
Ranks: 1, 2, 2, 3"Values: 5, 5, 10, 15"
Ranks: 3, 3, 2, 1Frequently asked questions
Can I use this formula for large datasets?
Yes, this formula works effectively for large datasets.
What if I have blank cells in my data?
Blank cells will be ignored in the ranking process.
Does this formula work with negative numbers?
Yes, it ranks negative numbers correctly just like positive numbers.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.