RANK.AVG Function

The RANK.AVG function returns the rank of a numeric value compared to a list of other numeric values. RANK.AVG can rank values from largest to smallest (i.e., the rank of highest test scores) and smallest to largest (i.e., the rank of fastest times in a race). When RANK.AVG encounters duplicates, it will assign an average rank to each set of duplicates. RANK.AVG works fine with sorted or unsorted data — it is not necessary to sort the values in the list before RANK.AVG.

Caution - The RANK.AVG function shares a key limitation with *IFS functions like SUMIFS, COUNTIFS, and MAXIFS. Unlike most functions, these functions require actual cell ranges for their range arguments. They won't accept arrays created by functions like SEQUENCE, HSTACK, or FILTER. For example, this formula won't work:

=RANK.AVG(1,SEQUENCE(10)) // won't work

Even though SEQUENCE(10) generates a list of numbers, it's not a valid range. This limitation wasn’t as noticeable before dynamic arrays, but it's more relevant now with newer Excel functions.

Synax

The basic syntax for RANK.AVG looks like this:

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

where number is the value you want to rank, ref is a range that contains numbers to rank against, and order is an optional argument for controlling the ranking direction. By default, RANK.AVG will rank values in descending order and assign an average rank to tied values in the list. This behavior can be reversed using the optional order argument as explained below.

Note: The RANK.AVG function is designed to handle ties by assigning them an average rank, which is different from RANK.EQ or RANK. This is the only difference between RANK.EQ and RANK.

Ranking in descending or ascending order

The RANK.AVG function has two modes of operation, descending and ascending, which are controlled by the order argument. To rank values where the largest value is ranked number 1, set order to zero (0) or omit the argument altogether:

=RANK.AVG(A1,range) // rank descending (default)
=RANK.AVG(A1,range,0) // rank descending

To rank values where the smallest value should be 1, set order to 1:

=RANK.AVG(A1,range,1) // rank ascending

To recap: set order to zero (0) when you want to rank something like top sales, where the largest sales number should rank #1, and set order to one (1) when you want to rank something like race results, where the smallest (fastest) time should rank #1.

Example - ranking test scores in descending order

In the worksheet below, the goal is to rank test scores. For test scores, the highest score should be assigned a rank of 1, so the RANK.AVG function is used in its default mode. The formula in cell D5 is:

=RANK.AVG(C5,$C$5:$C$12)

Notice that the range is provided as the absolute reference $C$5:$C$12 so that it won't change when the formula is copied down. The optional order argument is not provided, since RANK.AVG will assign an average rank to tie values by default.

Example - ranking race times in ascending order

In the example below, the goal is to rank race times. This is an example of where we want to assign a rank of 1 to the fastest time, which will be the smallest time. The formula in cell D5 is:

=RANK.AVG(C5,$C$5:$C$12,1)

Notice the range $C$5:$C$12 is absolute to prevent the reference from changing when the formula is copied down while the reference to C5 is relative so that it will change as the formula is copied down. Also, note that the order argument is provided as 1 to force RANK.AVG to rank the times in ascending order.

Ranking ties

When the data contains duplicate (tied) values, you have to decide how you want to rank tied values. There are two basic approaches: (1) assign the same rank to each tie value or (2) assign an average rank to each tie value. The RANK.AVG function is designed to follow the second approach. For example, in the data {90,85,95,80}, there are no ties and RANK.AVG would assign ranks of {2,3,1,4}. In the data {85}, there are two tied values and RANK.AVG would assign ranks of {2,3.5,1,3.5}. One advantage of using an average rank is that the sum of ranks (10) is preserved.

As mentioned above, the RANK.AVG function handles ties by assigning an average rank to tied values. For example, if two or more values in the data have the same rank, RANK.AVG will assign them an average rank rather than giving them the same rank. You can see how this works in the worksheet below, Aisha and Mia are tied for third place with a score of 91 and RANK.AVG assigns a rank of 3.5 to each. Notice the subsequent rank of 4 is skipped entirely:

Note: the fact that RANK.AVG averages tie values is the main reason to use RANK.AVG, since in other respects it operates like RANK and RANK.EQ. 

RANK.AVG versus RANK and RANK.EQ

Excel contains three functions for assigning rank: RANK, RANK.EQ, and RANK.AVG. RANK is the original ranking function in Excel. RANK.EQ and RANK.AVG were introduced in Excel 2010 as part of a broader effort by Microsoft to make Excel functions more consistent and intuitive. RANK and RANK.EQ are essentially the same function. There should be no cases where RANK and RANK.EQ return different results. RANK and RANK.EQ will assign tie values the same rank and the "EQ" in the name indicates this "equal rank" behavior. In contrast, the RANK.AVG function will assign tie values an average rank. For example, if two numbers are tied for third place, RANK.AVG will assign both numbers a rank of 3.5. The "AVG" in the name denotes the "average rank" behavior.

Recommendations

• The RANK function should continue to work fine in older worksheets. If you are updating an older worksheet, you could optionally replace RANK with RANK.EQ and get the same behavior and results.
• In new worksheets, Microsoft recommends RANK.EQ instead of RANK, since there is a possibility that RANK will be unsupported at some point in the future. Both RANK.EQ and RANK will return the same results and both will assign the same rank to tie values.
• If you specifically want an average rank for tie values in the data (i.e. duplicates) use the RANK.AVG function. This is the primary reason to use RANK.AVG since in other respects it works the same as RANK.EQ and RANK.
• The names for RANK.EQ and RANK.AVG identify the expected ranking behavior explicitly. So, while the names seem more complicated and fussy at first glance, they do have a purpose.

Notes

• The default for order is zero (0). If order is 0 or omitted, number is ranked against the numbers sorted in descending order, where ties are given an average rank, reflecting their equal standing.
• If order is 1, number is ranked against the numbers sorted in ascending order, again with ties receiving an average rank.
• Sorting values in the list before using the RANK.AVG function is not necessary.
• RANK.AVG averages ties and preserves the "sum of ranks".
• Some documentation suggests ref can be an array, but in our testing, ref must be a range. Otherwise, Excel will display the "There's a problem with this formula" error dialog.