Rank Formula in Excel
- 0 Comments
The Rank Formula that is built into Excel can be a very useful way of speeding up the ranking of several items. Let’s take a look at the formula and see how this can help us out:
=rank(number, ref, order)
where number is the number whose rank you want to find; ref is a reference to the range of numbers you want to rank against (non-numeric values in the range are ignored), and order refers to the way you want to ranking to appear - 0 or blank for descending and a non-zero number for ascending.
EXAMPLES
Example 1: =rank(B2,B2:B7) will return 2. Since we left the order reference blank it ranked in descending order and 97% is the second highest score in the list.
Example 2: =rank(B3,B2:B7,1) will return 1. Since this time we filled in order with a non-zero number it returned in ascending order and 67% was the lowest score.
Example 3: In this example, please refer to the image below of the updated spreadsheet. To obtain this result, all we had to do was take the formula in example 1 and lock some values (=rank(B2, $B$2:$B$7). After you enter this in cell C2, you just need to pull the formula down and it will fill the rest in correctly.
Further Applications
There are many other circumstances where you can use the Rank Formula in Excel. One example that I use it for all the time is to create percentiles (There is actually a percentile function and a percentrank function, which I’ll explore more soon).
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
Loading ...