Excel RANK functions with formula examples

4 things you should know about RANK in Excel

1. Any Rank formula in Excel works only for numeric values: positive and negative numbers, zeros, date and time values. Non-numeric values in the ref argument are ignored.
2. All RANK functions return the same rank for duplicate values, and skip subsequent ranking, as shown in the example below.
3. In Excel 2010 and later versions, the RANK function has been replaced with RANK.EQ and RANK.AVG. For backward compatibility, RANK still works in all versions of Excel, but it may not be available in the future.
4. If number is not found within ref, any Excel Rank function would return the #N/A error.

How to rank in Excel from lowest to highest

As shown in the above example, to rank numbers from highest to lowest, you use one of the Excel Rank formulas with the order argument set to 0 or omitted (default).

To have number ranked against other numbers sorted in ascending order, put 1 or any other non-zero value in the optional third argument.

For example, to rank the 100-meter sprint times of the students, you can use either of the below formulas:

=RANK(B2,$B$2:$B$7,1)

=RANK.EQ(B2,$B$2:$B$7,1)

Please pay attention that we lock the range in the ref argument by using absolute cell references, so that it won't change when we copy the formula down the column.

As the result, the lowest value (fastest time) is ranked 1st and the largest value (slowest time) gets the lowest rank of 6. The equal times (B2 and B7) are given the same rank.

How to rank data in Excel uniquely

As pointed out earlier, all of the Excel Rank functions return the same rank for items of equal value. If that's not want you want, use one of the following formulas to resolve tie-break situations and give a unique rank to each number.

Ranking in Excel based on multiple criteria

The previous example has demonstrated two working solutions for an Excel RANK tie break situation. However, it may seem unfair that equal numbers are ranked differently based solely on their position in the list. To improve your ranking, you may want to add one more criteria to be considered in case of a tie.

In our sample dataset, let's add total scores in column C and calculate rank as follows:

• First, rank with Math Score (main criteria)
• When there is a tie, break it with Total Score (secondary criteria)

To have it done, we will be using a regular RANK/RANK.EQ formula to find rank, and the COUNTIFS function to break a tie:

=RANK.EQ($B2,$B$2:$B$7) + COUNTIFS($B$2:$B$7, $B2, $C$2:$C$7, ">"& $C2)

Compared to the above example, this rank formula is more objective: Timothy is ranked 2^nd because his total score is higher than that of Julia:

How to calculate percentile rank in Excel

In statistics, a percentile (or centile) is the value below which a certain percentage of values in a given dataset falls. For example, if 70% of students are equal to or below your test score, your percentile rank is 70.

To get percentile rank in Excel, use the RANK or RANK.EQ function with a non-zero order argument to rank numbers from smallest to largest, and then divide the rank by the count of numbers. So, the generic Excel Percentile Rank formula goes as follows:

To calculate the percentile rank of our students, the formula takes the following shape:

=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)

To have the results displayed correctly, be sure to set the Percentage format to the formula cells:

How to rank numbers in non-adjacent cells

In situations when you need to rank non-contiguous cells, supply those cells directly in the ref argument of your Excel Rank formula in the form of a reference union, locking the references with the $ sign. For example:

=RANK(B2,($B$2,$B$4,$B$6))

To prevent errors in non-ranked cells, wrap RANK in the IFERROR function, like this:

=IFERROR(RANK(B2,($B$2,$B$4,$B$6)), "")

Please notice that a duplicate number is also given a rank, though cell B5 is not included in the formula:

If you need to rank multiple non-contiguous cells, the above formula may become too long. In this case, a more elegant solution would be defining a named range, and referencing that name in the formula:

=IFERROR(RANK(B2,range), "")

How to rank in Excel by group

When working with entries organized into some kind of data structure, data may belong to various groups, and you might want to rank numbers within each group individually. The Excel RANK function cannot resolve this challenge, so we are going to use a more complex SUMPRODUCT formula:

Rank by group in descending order:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Rank by group in ascending order:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1

Where:

• A2:A7 are groups assigned to numbers.
• C2:C7 are numbers to be ranked.

In this example, we use the first formula to rank numbers in each group from largest to smallest:

How this formula works
Essentially, the formula evaluates 2 conditions:

• First, you check the group (A2=$A$2:$A$7). This part returns an array of TRUE and FALSE based on whether a range element belongs to the same group as A2.
• Second, you check the score. To rank values from largest to smallest (descending order), use the condition (C2<$C$2:$C$11), which returns TRUE for the cells greater than or equal to C2, FALSE otherwise.

Since in Microsoft Excel terms, TRUE = 1 and FALSE = 0, multiplying the two arrays gives an array of 1's and 0's, where 1 is returned only for the rows in which both conditions are met.

Then, SUMPRODUCT adds up the elements of the 1's and 0's array, hence returning 0 for the largest number in each group. And you add 1 to the result to start ranking with 1.

The formula that ranks numbers within groups from smallest to largest (ascending order) works with the same logic. The difference is that SUMPRODUCT returns 0 for the smallest number in a particular group, since no number in that group meets the 2^nd condition (C2>$C$2:$C$7). Again, you replace zero rank with the 1st rank by adding 1 to the formula result.

Instead of SUMPRODUCT, you can use the SUM function to add up the array elements. But this would require using an array formula, completed via Ctrl + Shift + Enter. For example:

=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

How to rank positive and negative numbers separately

If your list of numbers contains both positive and negative values, the Excel RANK function will rank them all in no time. But what if you'd like to have positive and negative numbers ranked separately?

With numbers in cells A2 to A10, use one of the following formulas to get individual ranking for positive and negative values:

How to rank data in Excel ignoring zero values

As you already know, a RANK formula is Excel handles all numbers: positive, negative and zeros. But in some cases, we just want to rank cells with data ignoring 0 values. On the web, you can find a few possible solutions for this task, but the Excel RANK IF formula, methinks, is the most universal one:

How to calculate rank in Excel by absolute value

When dealing with a list of positive and negative values, there may be a need to rank numbers by their absolute values ignoring the sign.

The task can be fulfilled with one of the bellow formulas, at the heart of which is the ABS function that returns the absolute value of a number:

How to get N largest or smallest values

If you wish to get an actual N number of the largest or smallest values rather than their ranking, use the LARGE or SMALL function, respectively.

For example, we can get the top 3 scores of our students with this formula:

=LARGE($B$2:$B$7, $D3)

Where B2:B7 is the list of scores and D3 is the desired rank.

Additionally, you can retrieve the students' names by using the INDEX MATCH formula (provided there are no duplicate scores in the top 3):

=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))

Similarly, you can use the SMALL function to pull the bottom 3 values:

=SMALL($B$2:$B$7, $D3)

That's how you do ranking in Excel. I thank you for reading and hope to see you on our blog next week!