RANK.EQ vs RANK.AVG: How Excel Handles Ties

RANK.EQ vs RANK.AVG comes down to one question: what should tied values get? Both functions tell you where a number sits in a list, and they give identical answers until two or more values are equal. RANK.EQ gives ties the best rank they share, while RANK.AVG gives them the average of their positions. This guide shows both on the same data so you can pick the right one for a leaderboard, a report or a statistics task.

RANK.EQ vs RANK.AVG at a glance#

The two functions share their syntax, their versions and their errors. Only the tie rule is different.

How both functions work#

Both functions take the same three arguments:

• number is the value you want to rank, such as B2.
• ref is the full list to compare against, such as $B$2:$B$8. Lock it with dollar signs so it doesn't move when you fill down.
• order is optional. Leave it out or use 0 to make the largest value rank 1. Use 1 to make the smallest value rank 1, for times or costs.

Both ignore text and blank cells in the list. Both return #N/A if the number isn't in the list. RANK is the older name for RANK.EQ and still works, as the guide to the RANK function explains.

The same scores ranked both ways#

Here are seven reps with test scores. Ana and Ben tie, and Cara, Dev and Eli tie:

Column C uses =RANK.EQ(B2,$B$2:$B$8) and column D uses =RANK.AVG(B2,$B$2:$B$8), both filled down.

Read it as positions in a sorted list. Fay is 1st. Ana and Ben take positions 2 and 3. RANK.EQ gives both the better one, 2. RANK.AVG gives both the middle, (2+3)/2 = 2.5. Cara, Dev and Eli take positions 4, 5 and 6. RANK.EQ gives all three 4, and RANK.AVG gives (4+5+6)/3 = 5. Gus is 7th in both.

Notice the totals. RANK.EQ adds up to 24, because the shared top ranks pull the total down. RANK.AVG adds up to 28, the same as 1 + 2 + … + 7 with no ties at all. That is why statistics uses average ranks.

Ranking from smallest to largest#

Add 1 as the order argument and the ties work the same way, counted from the bottom. Gus is 1st. Cara, Dev and Eli take positions 2 to 4, so RANK.EQ gives 2 and RANK.AVG gives 3. Ana and Ben take 5 and 6, so they get 5 and 5.5.

How to rebuild RANK.AVG from RANK.EQ#

You can calculate the average rank yourself, which shows exactly what RANK.AVG does. A tie that starts at rank r and has c values covers ranks r to r + c − 1, and their average is r + (c − 1)/2:

=RANK.EQ(B3,$B$2:$B$8)+(COUNTIF($B$2:$B$8,B3)-1)/2

For Ben, RANK.EQ returns 2 and COUNTIF finds 2 scores of 920. So the result is 2 + (2 − 1)/2 = 2.5, the same as RANK.AVG. For a value with no tie, COUNTIF returns 1 and nothing is added. This also works with the order argument set to 1.

When to use RANK.EQ#

Use RANK.EQ when people will read the ranks as places. It matches how most competitions work: two runners who tie for 2nd both get silver, and nobody is 3rd.

• Leaderboards and sales contests. "Joint 2nd" is easy to understand. "2.5th" is not.
• Top-N filters. A rule like "rank 3 or better gets a bonus" is clearer with whole numbers.
• Old files. If people open the workbook in Excel 2007 or earlier, use plain RANK, which behaves like RANK.EQ.
• Unique ranks. Add a COUNTIF tie-breaker, =RANK.EQ(B2,$B$2:$B$8)+COUNTIF($B$2:B2,B2)-1, to give every row its own rank.

To get the values or names at each place rather than the rank of each value, LARGE and SMALL are often a better fit. See RANK vs LARGE and the guide to top N lists with LARGE and SMALL.

When to use RANK.AVG#

Use RANK.AVG when the ranks feed further calculations, especially statistics.

• Rank-based statistics. Spearman's rank correlation and tests like the Mann–Whitney U test give tied values the average of their ranks. RANK.AVG does that for you.
• Fair scoring across judges. When several judges rank the same entries and you average their ranks, average ranks treat tied entries evenly.
• Comparing rank totals. Because RANK.AVG always sums to the no-tie total, rank totals for different groups stay comparable.

Average ranks are close cousins of the median: both describe a middle position. If that idea is new, the guide to AVERAGE vs MEDIAN explains the difference between averages and middle values.

Common mistakes with RANK.EQ and RANK.AVG#

These slips cause most wrong ranks:

1. Not locking the list. Without $B$2:$B$8, the list shifts as you fill down and ranks drift. Press F4 (Mac: ⌘T) to add the dollar signs.
2. Ties that aren't really ties. Values like 0.1+0.2 and 0.3 can differ in the last decimal place, so they don't tie. Round the values first with ROUND.
3. Numbers stored as text. Text is ignored in the list, and ranking a cell that holds text returns an error. Convert the data with VALUE.
4. Mixing up the order argument. For times, costs or errors, where smaller is better, use 1.
5. Expecting the results in sorted order. Ranking doesn't move the rows. To show a sorted leaderboard in Excel 2021 or Microsoft 365, use =SORTBY(A2:A8,B2:B8,-1), covered in the guide to SORT and SORTBY.

Our recommendation#

Use RANK.EQ by default. It matches how people read places, it returns whole numbers, and it behaves like the older RANK function that most templates use. Switch to RANK.AVG only when the ranks go into a calculation that expects average ranks for ties, such as Spearman's correlation or another rank-based test. If you need every row to have a different rank, neither function does that alone: use RANK.EQ plus a COUNTIF tie-breaker.

Practise it#

Ranking makes most sense next to the functions that return values by position. Practise LARGE and SMALL in a live spreadsheet: you pull top and bottom values from a list and the sheet checks each formula instantly. Then turn ranks into a sorted leaderboard in the SORT function lesson, or rebuild RANK.AVG in the exercise on this page.