RANK.AVG Function

Summary

RANK.AVG returns the rank of a number in a list. The special part is tie handling. When multiple values are tied, Excel gives them the average of the ranks they would have occupied instead of giving them the same top rank style used by RANK.EQ.

That means the result can be a decimal. If two values would normally land in positions 2 and 3, RANK.AVG returns 2.5 for both. This makes the ranking feel more balanced when ties matter.

RANK.AVG is useful when tied values should share a fair middle rank instead of all taking the same top-most rank. That makes it helpful in scoreboards and comparisons where ties exist and the ranking method itself should stay balanced.

Syntax

=RANK.AVG(number, ref, [order])

number is the value you want to rank, ref is the full comparison list, and order controls direction. Leave order blank or use 0 for descending order, and use 1 for ascending order.

Arguments

• number - [required] The value whose rank you want.
• ref - [required] The list or range of numeric values used for comparison.
• order - [optional] Use 0 or omit it for largest to smallest, or use 1 for smallest to largest.

RANK.AVG vs Other Functions

The practical difference between RANK.AVG and RANK.EQ is tie style. Both rank correctly, but they express ties differently.

Using the RANK.AVG Function

This function is useful for scoreboards, standings, sales tables, turnaround times, or any list where ties should be treated more evenly. It is also helpful when the ranking will feed into later analysis and you want tie positions represented more smoothly than the equal-rank version.

Microsoft notes that if order is 0 or omitted, Excel ranks as if the list were sorted descending. If order is any nonzero value, Excel ranks as if the list were sorted ascending. Microsoft also notes that nonnumeric values in ref are ignored.

• Use descending rank for scores, sales, or any case where bigger is better.
• Use ascending rank for costs, wait times, or any case where smaller is better.
• Lock the reference range with dollar signs if you plan to fill the formula down.

Example 1 - Rank a value in descending order

This example uses the default ranking direction, where larger values rank higher. That means the biggest score, sale, or result in the list gets position 1.

This is the normal setup for leaderboards and performance tables because it answers the question, "How high does this value place compared with the rest?"

=RANK.AVG(B1,$B$1:$B$10) // Returns the rank of B1 from highest to lowest.

Example 2 - Rank a value in ascending order

This example flips the ranking direction by using order 1. Now the smallest value gets rank 1 instead of the largest value.

That makes this version better for costs, delays, times, or error counts, where a lower number is actually the better result.

=RANK.AVG(B1,$B$1:$B$10,1) // Returns the rank of B1 from lowest to highest.

Example 3 - See the averaged tie rank

This example shows the main reason to use RANK.AVG instead of RANK.EQ. When two values tie, Excel averages the positions they would have taken.

That is why the result can be a decimal such as 2.5. It is a fairer way to rank ties when you do not want both values to share one whole-number position with the next rank skipped.

=RANK.AVG(B1,$B$1:$B$5) // Returns an averaged rank when ties exist.

Example 4 - Check a sales standing

This example uses ranking as a quick standing check for one specific result inside a larger list. Instead of reviewing the whole table, the formula tells you where that one sale sits in the wider leaderboard.

That is useful in dashboards and summary cells where you want one clear position number without sorting or manually reading the list each time.

=RANK.AVG(B1,$B$1:$B$10) // Returns the standing of B1 in the list.

Conclusion Recap

RANK.AVG is mainly about ranking with fairer tie handling. In this lesson, the important difference was not the rank itself, but what happens when two values land in the same place.

Instead of giving both tied values the same top-style rank, Excel averages the positions they would have taken. That is why the result can be a decimal, and that is what makes RANK.AVG different from RANK.EQ.

• Main job: RANK.AVG ranks a value and averages tied positions.
• Default direction: Omit order or use 0 for descending rank.
• Ascending option: Use 1 when smaller values should rank higher.
• Tie behavior: Ties can return decimal ranks such as 2.5.
• Good use cases: Fair leaderboards, standings, and score comparisons.