The tutorial explains the specificities of the Excel Rank functions and shows how to do ranking in Excel based on multiple criteria, rank data by group, calculate percentile rank, and more.
When you need to determine the relative position of a number in a list of numbers, the easiest way is to sort the numbers in ascending or descending order. If for some reason sorting is not possible, a Rank formula in Excel is the perfect tool to do the job.
The RANK function in Excel returns the order (or rank) of a numeric value compared to other values in the same list. In other words, it tells you which value is the highest, the second highest, etc.
In a sorted list, the rank of a certain number would be its position. The RANK function in Excel can determine the rank starting with the largest value (as if sorted in descending order) or the smallest value (as if sorted in ascending order).
The syntax of the Excel RANK function is as follows:
Number (required) - the value whose rank you'd like to find.
Ref (required) - a list of numeric values to rank against. It can be supplied as an array of numbers or a reference to the list of numbers.
Order (optional) - a number that specifies how to rank values:
RANK.EQ is an improved version of the RANK function, introduced in Excel 2010. It has the same syntax as RANK and works with the same logic: if several values are ranked equally, the highest rank is assigned to all such values. (EQ stands for "equal").
In Excel 2007 and lower versions, you should always use the RANK function. In Excel 2010, Excel 2013, and Excel 2016, you can go with either RANK or RANK.EQ. However, it'd be wise to use RANK.EQ because RANK can be discontinued at any moment.
RANK.AVG is another function to find rank in Excel that is available only in Excel 2010, Excel 2013, Excel 2016 and later.
It has the same syntax as the other two functions:
The difference is that if more than one number has the same rank, the average rank is returned (AVG stands for "average").
To gain more understanding about ranking data in Excel, please have a look at this screenshot:
All three formulas rank numbers in column B in descending order (the order argument omitted):
In all versions of Excel 2003 - 2016:
In Excel 2010 - 2016:
The difference is in how these formulas process duplicate values. As you see, the same score appears twice, in cells B5 and B6, which affects subsequent ranking:
The path to excellence, they say, is paved with practice. So, to better learn how to use RANK function in Excel, alone or in combination with other functions, let's work out solutions to a few real-life tasks.
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:
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.
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.
To rank the math scores of our students uniquely in descending order, use this formula:
To rank the 100-meter race results in ascending order with no duplicates, use this formula:
=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1
How these formulas work
As you may have noticed, the only difference between the two formulas is the order argument of the RANK.EQ function: omitted to rank values descending, 1 to rank ascending.
In both formulas, it's the COUNTIF function with the clever use of relative and absolute cell references that does the trick. In short, you use COUNTIF to find out how many occurrences of the number being ranked there are in the above cells, including the number's cell. In the topmost row where you enter the formula, the range consists of a single cell ($B$2:B2). But because you lock only the first reference ($B$2), the last relative reference (B2) changes based on the row where the formula is copied. Thus, for row 7, the range expands to $B$2:B7, and the value in B7 is compared to each of the above cells.
Consequently, for all 1st occurrences, COUNTIF returns 1; and you subtract 1 at the end of the formula to restore the original rank.
For 2nd occurrences, COUNTIF returns 2. By subtracting 1 you increment the rank by 1 point, thus preventing duplicates. If there happen to be 3 occurrences of the same value, COUNTIF()-1 would add 2 to their ranking, and so on.
Another way to rank numbers in Excel uniquely is by adding up two COUNTIF functions:
For example, to rank numbers uniquely from highest to lowest, you'd use this formula:
As shown in the screenshot below, the tie-break is successfully resolved, and a unique rank is assigned to each student:
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:
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:
Compared to the above example, this rank formula is more objective: Timothy is ranked 2nd because his total score is higher than that of Julia:
The RANK part of the formula is obvious, and the COUNTIFS function does the following:
Because COUNTIFS works with the AND logic, i.e. counts only cells that meet all of the specified conditions, it returns 0 for Timothy since no other student with the same Math score has a higher total score. Hence, Timothy's rank returned by RANK.EQ is unchanged. For Julia, the COUNTIFS function returns 1 because one student with the same Math score has a higher total, so her rank number is incremented by 1. If one more student had the same Math score and a total score lower than that of Timothy and Julia, his/her rank would be incremented by 2, and so on.
Instead of the RANK or RANK.EQ function, you could use COUNTIF to check the main criteria, and COUNTIFS or SUMPRODUCT to resolve a tie break:
The result of these formulas are exactly the same as shown above.
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:
To have the results displayed correctly, be sure to set the Percentage format to the formula 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:
To prevent errors in non-ranked cells, wrap RANK in the IFERROR function, like this:
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:
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:
Rank by group in ascending order:
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:
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 2nd condition (C2>$C$2:$C$7). Again, you replace zero rank with the 1st rank by adding 1 to the formula result.
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:
The results will look something similar to this:
How these formulas work
To begin with, let's break down the formula that ranks positive numbers in descending order:
In this example, A2 contains the 2nd highest positive number, for which COUNTIF returns 1, meaning there is just one number greater than it. In order to start our ranking with 1, not 0, we add 1 to the formula result, so it returns a rank of 2 for A2.
The formula that ranks positive numbers in ascending order works a bit differently:
If the number is greater than 0, the first COUNTIF gets the total count of positive numbers in the data set, and the second COUNTIF finds out how many values are higher than that number. Then, you subtract the latter from the former, and get the desired rank. In this example, there are 5 positive values, 1 of which is greater than A2. So, you subtract 1 from 5, thus getting a rank of 4 for A2.
Formulas to rank negative numbers are based on a similar logic.
Note. All of the above formulas ignore zero values because 0 belongs neither to the set of positive nor to the set of negative numbers. To include zeros in your ranking, replace >0 and <0 with >=0 and <=0, respectively, where the formula logic requires.
For example, to rank positive numbers and zeros from largest to smallest, use this formula:
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:
=IF($B2=0,"",IF($B2>0,RANK($B2,$B$2:$B$10,1) - COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))
Where B2:B10 is the range of numbers to be ranked.
The best thing about this formula is that it works beautifully both for positive and negative numbers, leaving zero values out of ranking:
How this formula works
At first sight, the formula may seem a bit tricky. Upon a closer look, the logic is very simple.
Here's how the Excel RANK IF formula ranks numbers from largest to smallest ignoring zeros:
Yep, it's that easy! The formula to ranks numbers from smallest to largest ignoring zeroes works in a similar manner, and it could be a good brain exercise to deduce its logic :)
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:
=SUMPRODUCT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>"")) - SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>""))+1
=SUMPRODUCT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>"")) - SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>""))+1
As the result, negative numbers are ranked as if they were positive numbers:
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:
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):
Similarly, you can use the SMALL function to pull the bottom 3 values:
That's how you do ranking in Excel. To better understand and probably reverse-engineer the formulas discussed in this tutorial, you are welcome to download our Sample Rank Excel workbook.
I thank you for reading and hope to see you on our blog next week!
Table of contents