RANK Function
Returns the statistical rank of a given value within an array of values
Returns the statistical rank of a given value within an array of values
The RANK Function is categorized as a Statistical function. The function returns the statistical rank of a given value within a supplied array of value. Thus, it determines the position of a value in an array.
=RANK(number,ref,[order])
The RANK function uses the following arguments:
Before we proceed, we need to know that the RANK function’s been replaced by RANK.EQ and RANK.AVG. To enable backward compatibility, RANK still works in Excel 2016 (latest version), but it may not be available in future. If you type this function in Excel 2016, it will show a yellow triangle with an exclamation point.
As a worksheet function, RANK can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let’s consider a few examples:
Assuming we are given a list of employees with their respective medical reimbursement expenses. We wish to rank them according to total expenditure.
To rank in descending order, we will use the formula =RANK(B2,($C$5:$C$10),0), as shown below:
The result we get is shown below:
As seen above, the RANK function gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, as shown above, in a list of integers sorted in ascending order, the number 100 appears twice with a rank of 4. The next value (25) will be ranked 6 (no number will be ranked 5).
If we want unique ranks, we can use the formula:
=RANK(C5,$C$5:C$10,0)+COUNTIF($C$5:C5,C5)-1
We will get the results below:
For ascending order, the formula would be:
=RANK.EQ(C5,$C$5:C$10,1)+COUNTIF($C$5:C5,C5)-1
In both formulas, it’s the COUNTIF function that does the trick. We used COUNTIF to find out the number of times the number occurred that was ranked. In COUNTIF formula, the range consists of a single cell ($C$5:C5). As we locked only the first reference ($C$5), the last relative reference (C5) changes based on the row where the formula is copied. Thus, for row 7, the range expands to $C$5:C10, and the value in C10 is compared to each of the above cells.
Thus, for all unique values and first occurrences of duplicate values, COUNTIF returns 1; and we subtract 1 at the end of the formula to restore the original rank.
For ranks occurring the second time, COUNTIF returns 2. By subtracting 1, we increased the rank by 1 point, thus preventing duplicates. If there happen to be 3 plus occurrences of the same value, COUNTIF()-1 would add 2 to their ranking, and so on.
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!