You hold the sales of twenty reps and your boss asks who finishes third in each region. Sorting the table breaks the other columns and still does not answer the region part.
How to use Rank Formula step by step
1. Choose the kind of ranking
Classic, modern, ties averaged, no gaps after a tie, within a group or flagging the leaders.
2. Give the cell and the range
The cell is the row you want ranked; the range holds every value. The tool pins it with $ so you can drag.
3. Set the order and copy
Highest first for sales or points, lowest first for times or costs. Paste the formula and drag it down the column.
The Step Count: Traditional Friction vs. KorexTools
Sorting the table and numbering by hand (4 steps)
- Sort by the sales column.
- Type 1, 2, 3 beside it.
- Sort back by name and lose the numbering.
- Redo it all whenever the data changes.
With the KorexTools “Rank Formula” builder (3 steps)
- Pick the kind of ranking.
- Mark the cell and the range.
- Copy it and the position updates itself.
Rankings break where ties live, and ties are the norm
RANK gives two equal values the same position and skips the next: with 90, 85, 85 and 70 the last one is fourth, not third. That is right in a race, but annoying when you want to number categories. For that case there is the gap-free version, which counts distinct values using SUMPRODUCT.
A per-group ranking needs another idea: count how many rows in the same group hold a higher value and add one. The tool builds it with COUNTIFS, and the top-N option wraps an IF around the ranking to label only those inside the cut.
Which ranking suits what
- •An overall standing with everything ordered: RANK or RANK.EQ.
- •Ties that should split the position: RANK.AVG.
- •Numbering without holes or ranking inside each region: the gap-free and per-group versions.
Values 90, 85, 85 and 70 in B2:B5
| Method | Formula | Positions |
|---|---|---|
| Classic | =RANK(B2,$B$2:$B$5,0) | 1, 2, 2, 4 |
| Ties averaged | =RANK.AVG(B2,$B$2:$B$5,0) | 1, 2.5, 2.5, 4 |
| No gaps | =SUMPRODUCT(($B$2:$B$5>B2)/COUNTIF($B$2:$B$5,$B$2:$B$5))+1 | 1, 2, 2, 3 |
Common Mistakes and How to Avoid Them
- ⚠️Forgetting to pin the range with $ and watching it slide as you drag.
- ⚠️Including the totals row in the range, which always comes first.
Frequently Asked Questions
Which order do I choose for race times?
Lowest first. With that order the smallest value takes position 1, which is what you expect for times, costs or deadlines.
Why are there two equal positions and one missing?
Because RANK gives tied values the same position and skips the next. If you do not want gaps, use the "No gaps after a tie" option.
Are RANK and RANK.EQ different?
They return the same result. The first is the legacy one, valid in any version; the second is current and better for new workbooks.
What I would do if I were in your shoes
«A ranking with no declared tiebreak ends in an argument; settle the tiebreak before publishing the table.» — DataKorex Engineering Team.