The excel rank function returns the rank of a numeric value compared to other numerical value within a specified range of values. You can rank in Ascending or descending order with the RANK Function, and the function is very useful in data analysis for sorting an filtering data by rank and identifying the highest and lowest values within a data set.
In today’s post we would take you through all you need to know about the RANK Function including the Syntax & Argument, Usage notes and some examples. Let’s start things off with the syntax and argument.
Syntax & Argument
The syntax of the RANK function is as follows:
=RANK(number,ref,[order])
The RANK Function has 3 arguments, 2 are required whilst the third is optional. Let’s have a look at these arguments:
number: This is the individual value you want to rank
ref: The range containing the numerical values (of which the first argument (number) is a part of)
[order]: This is the optional argument that is used to specify the order of ranking (ascending or descending). The default is descending order if omitted.
Usage Notes
In this section, we would review some of the things to note when using the RANK Function. Let’s begin shall we:
- The RANK Function assigns the same rank to duplicate values. This means that if the same numerical value appears more than once, let’s say the number 50, then all three occurrences would have the same rank. Assuming 50 is the highest value in the range and we are ranking by descending order, then those three occurrences would have the rank of 1.
- In the first note, we talked about multiple occurrences of a numerical value having the same rank. What happens if the next value, say 49? With the RANK function, 49 gets a rank of 4, rather than a rank of 2.
- If a one or more cells within the range are blank or contain an error, the RANK Function will return an error.
RANK Function Examples
In this section of our examples, we are using some random numbers to illustrate how the RANK function works. We would be ranking the numbers in our range B3:B7 in the two modes of operation available. The first mode is Descending order and the second ascending order.
Ranking in Descending Order
Our task here is to rank the 5 numbers above in descending order. Please note that descending order means from largest to smallest. The formula to achieve a descending order rank is as follows:
Now we can copy and paste the formula to the rows below to see the position of each number in our range.
As we can see from the above image, 30 is the largest number so it has a rank of 1 whilst 2 is the smallest number with a rank of 5.
Ranking in Ascending Order
Ascending order is from smallest to largest, and to use the RANK Function to retrieve this, we only need to change one element in the formula we provided under ranking in descending order:
Copy the formula to the cells below to see each number’s rank
Since we are ranking in ascending order, excel assigns the rank of 1 to the smallest number. In our range of numbers, the smallest number is 2 and it is therefore assigned a rank of 1.
Ranking with Duplicates
For this section, we are using a new data set which has a Telecom company’s outbound minutes to 20 destinations, and we would see some destinations having the exact number of minutes.
Now, let’s rank these 20 destinations using their minutes of use and later we would sort from smallest rank to largest rank. To rank, we use the ranking in descending order formula format. Let’s pick cell C3, this means that we would use the Rank formula below:
Copy and paste to the other cells below (using Ctrl + C to copy and Ctrl + V to paste)
Now let’s sort from rank from smallest to largest which would actually be the inverse when it comes to minutes (i.e Largest to smallest minutes). Confused on how to sort? Please check out this post.
From the above, we can see that Nigeria, Kenya and Sri Lanka are assigned the rank of 12 because they have the same number of minutes. The next destination is Croatia which has lower number of minutes. You would however see that Croatia is assigned a number 15 because all 3 (Nigeria, Kenya and Sri Lanka) ranked 12 were counted even though they had the same rank, so the next available rank is 15 which Croatia occupies.
