Rank values with RANK and ties
RANK returns the position of a number within a list, and when two values tie they share the same rank and the next rank is skipped.
=RANK(B2,$B$2:$B$100,0)The sheet: Reps sheet: column A is Rep name and column B is Quarterly sales. You want each rep's position, with the highest sales ranked 1.
How it works, step by step
- 01B2 is the sales figure to rank.
- 02$B$2:$B$100 is the full list to compare it against. The $ signs keep it fixed when you fill down.
- 03The final 0 ranks in descending order, so the largest value gets rank 1. Use 1 to rank the smallest value first.
- 04With sales of 100, 90, 90, and 80 the ranks are 1, 2, 2, and 4. Rank 3 is skipped because two reps tied for second.
The same logic in four tools
=RANK(B2,$B$2:$B$100,0)=RANK(B2,$B$2:$B$100,FALSE)SELECT rep, sales,
RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM reps;
-- Use DENSE_RANK() to avoid skipped numbers after ties.import pandas as pd
reps["sales_rank"] = reps["sales"].rank(method="min", ascending=False)
# method="dense" avoids skipped numbers after ties.Mistakes that break it
- !Forgetting the $ signs lets the comparison range shift as you fill down, so each row is ranked against a different list.
- !Ties share a rank and leave a gap, which surprises people who expect 1, 2, 3, 4. Use DENSE_RANK in SQL or method="dense" in pandas if you do not want gaps.
- !RANK ignores text and blank cells in the list, but a blank in the cell you rank itself returns #N/A.
- !In Google Sheets the last argument is TRUE or FALSE for ascending. A 0 also works, but FALSE is clearer.
Common questions
- How do I rank without ties in Excel?
- Break the tie with a second condition: =RANK(B2,$B$2:$B$100,0)+COUNTIF($B$2:B2,B2)-1. The first of the tied rows keeps the rank and later rows get the next numbers.
- What is the difference between RANK, RANK.EQ and RANK.AVG?
- RANK and RANK.EQ behave the same, giving tied values the lowest rank. RANK.AVG gives tied values the average of the ranks they cover, such as 2.5 for a tie for second and third.
- How do I rank within a group?
- In SQL add PARTITION BY region inside OVER. In pandas use df.groupby("region")["sales"].rank(method="min", ascending=False).
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.