Did you ever wonder how to use Rank function Excel?
Every function has its significance that’s why you cannot overwrite the value of any procedure used in Excel. Talking about the Rank function helps in figuring out the ultimate sequence position of a cell from the given range appropriate for the numbers only. Rank is used for numbers only that’s why when you have 6 numbers and need to find the rank of any number, the range is needed to be selected so that you can choose the sequence in which rank you need.
The Syntax For Rank:
=RANK(number,ref,[order])
Below is the explanation of RANK arguments:
Number: It is a required argument used for a value that is needed to find the rank.
Ref: It is also a required argument that you can use as a list, an array, or a reference.
Order: It is not a required argument and is used on demand. It simply identifies the ranking procedure whether it is ascending or descending.
0 – you can use it in descending order
1 – you can use it in ascending order
In case the argument is not there, by default values of 0 will be added in descending order. A value other than zero will be presented in ascending order.
How to Use RANK Function Excel?
You can use the RANK function in a sheet as part of a formula. For a better understanding, let’s consider an example:
- Suppose, you have a list of employees in which you need to rank according to the net expenditure.
- Here is the formula you can use to rank in descending order:
=RANK(B2,($C$5:$C$10),0)
- Below is the result:
- When you use the RANK function, all duplicate numbers are assigned with similar ranks and of course, it clearly disturbs the rank of other numbers.
- Suppose you have a list of numbers presented in ascending order, the number 100 appears twice in the list and it is assigned a rank of 4. The next value is ranked as 6.
To get unique ranks, here is the formula:
=RANK(C5,$C$5:C$10,0)+COUNTIF($C$5:C5,C5)-1
Here are the results:
The formula for ascending order is given below:
=RANK.EQ(C5,$C$5:C$10,1)+COUNTIF($C$5:C5,C5)-1
The COUNTIF function is used in both formulas as it helps in finding how many times a number gets ranked. The range used in the COUNTIF formula consists of a single cell ($C$5:C5).
The COUNTIF function will give 1 for all the first occurrences and unique values. To get the original rank value, you can subtract the 1 at the end of the formula.
On the other hand, COUNTIF gives 2 for the second occurring value.
Types of RANK Functions:
Below are some types of RANK functions presented:
When you enter the RANK function in Excel, you will notice it presents 3 types of RANK functions:
- AVG
- EQ
- RANK
RANK.EQ Function
This kind of Rank is a highly improved version that came into being in Excel 2010. You will find similarities between the syntax of RANK.EQ and RANK with the same intent.
RANK.EQ(number,ref,[order])
The RANK function is useful for Excel 2007 and earlier versions. For later versions, you can use both RANK and RANK.EQ.
RANK.AVG Function
Another awesome kind of RANK function is used in Excel 2010, 2013, 2016, and later versions only. You will notice the similarities among the syntax of RANK.AVG function and the other two types.
RANK.AVG(number,ref,[order])
The average rank will be added if you see multiple numbers have the same rank.
Things to Know about the RANK in Excel:
As already mentioned, a RANK formula is used for numeric values only, whether these values are date and time values, or positive or negative numbers. If you add non-numeric values in the ref argument they are of no use.
Excel 2010 and later editions replace the RANK function with RANK.EQ and RANK.AVG.
You may encounter the #N/A error in case a number value is not added in the ref argument.
