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.

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 is the default behavior, so the highest value gets rank 1.

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

Example 2 - Rank a value in ascending order

Use order 1 when the smallest value should rank first.

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

Example 3 - See the averaged tie rank

If two values tie, Excel averages the positions instead of assigning the same top rank pattern.

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

Example 4 - Check a sales standing

This is a simple way to place one result inside a wider leaderboard.

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

Conclusion Recap

• 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.