RANK.EQ Function

The RANK.EQ function returns the numeric rank of a number when compared to a list of other numbers. You can use RANK.EQ when you want to assign a rank to a list of numeric values. RANK.EQ works fine with sorted or unsorted data. It is not necessary to sort the values in the list before using the RANK.EQ function. The basic syntax for RANK.EQ looks like this:

=RANK.EQ(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 that controls ranking direction. By default, RANK.EQ will rank values in descending order and assign 1 to the largest value in the list. However, this behavior can be reversed using the optional order argument as explained below.

Note: The RANK.EQ function is the recommended function for ranking in Excel, as RANK is now classified as a compatibility function. RANK is still available for backward compatibility. RANK.EQ and RANK are essentially the same function and there should be no cases where they return different results.

Ranking in descending or ascending order

The RANK.EQ 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.EQ(A1,range) // rank descending (default)
=RANK.EQ(A1,range,0) // rank descending

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

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

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 shortest (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.EQ function is used in its default mode. The formula in cell D5 is:

=RANK.EQ(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.EQ will assign 1 to the largest value by default.

Example - ranking race times in ascending order

In the example below, the goal is to 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.EQ(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 optional order argument is provided as 1 to force RANK.EQ to rank the times in ascending order.

Ranking ties

The RANK.EQ function handles ties in a specific manner: it assigns the same rank to both items. A tie occurs when two or more items in the data have the same value or, in other words, the data being ranked contains duplicates. For example, if a certain value has a rank of 3, and there are two instances of the value in the data, the RANK.EQ function will assign both instances a rank of 3. The next rank assigned will be 5, and no value will receive the rank of 4. The process looks like this:

1. Ties are identified - When two or more values in the list are the same, they are considered tied. For example, if two students have a score of 91, they are in a tie.
2. Ties are ranked - Excel assigns the same rank to the tied values. For instance, if two students are tied for the third-highest score, both will receive a rank of 3.
3. The subsequent rank is skipped - After a tie, Excel skips the next rank(s). If two students are tied for third place, the next student (with a lower score) receives a rank of 5, not 4. The tie essentially absorbs the fourth rank.

You can see the result of this process in the worksheet below, where Aisha and Mia have a score of 91 and are both tied for third place. The RANK.EQ function assigns both a rank of 3 and the next rank, 4, is skipped.

If tied ranks are a problem, one workaround is to employ a tie-breaking strategy.

RANK.EQ versus RANK and RANK.AVG

Excel contains three functions for assigning rank: RANK, RANK.EQ, and RANK.AVG. RANK.EQ is the updated function for ranking in Excel, recommended over the older RANK function for its consistency and intuitive use. RANK and RANK.EQ are essentially the same function, and there should be no cases where they return different results. RANK.AVG, on the other hand, assigns ranks to numeric values but provides a different behavior when there are tie values, assigning 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.

Recommendations

• For older worksheets that use RANK, consider replacing RANK with RANK.EQ to ensure consistent behavior and results.
• In new worksheets, use RANK.EQ instead of RANK to align with Microsoft's recommendations and to prepare for any future updates where RANK might be deprecated.
• Use RANK.AVG if you require an average rank for tie values in the data (i.e., duplicates). This is the only reason to use RANK.AVG since in other respects it works the same as RANK.EQ and RANK.

Notes

• The default for order is zero (0). If order is 0 or omitted, number is ranked against the numbers sorted in descending order: smaller numbers receive a higher rank value, and the largest value in a list will be ranked #1.
• If order is 1, number is ranked against the numbers sorted in ascending order: smaller numbers receive a lower rank value, and the smallest value in a list will be ranked #1.
• It is not necessary to sort the values in the list before using the RANK.EQ function.
• In the event of a tie (i.e., the list contains duplicates), RANK.EQ will assign the same rank value to each duplicate (tie) value and the next rank will be skipped.
• 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.