As a child of the dark ages, I used the word rank to describe something that smelled rotten or suspicious. Rank in Microsoft Excel is, thankfully, totally different. In Excel, rank is a value that represents the position of one value within a group of values. You’ll want to rank all kinds of data: student GPAs, sport statistics, product sales and so on. In this article, I’ll show you how to use Excel’s three ranking functions.
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
I’m using Microsoft 365 on a Windows 10 64-bit system. You can use Excel’s RANK() in any version. RANK.AVG and RANK.EQ() were added to Excel 2010 (.xlsx). I’m including demonstration files for both the .xlsx and .xls formats, but only RANK() will work in versions earlier than Excel 2010. Excel for the web supports all three ranking functions, for now.
How to use Excel’s RANK() function
Excel’s oldest ranking function, RANK() has been more or less replaced by more accurate functions, which we’ll look at later. For now, let’s look at RANK() to be comprehensive.
Excel’s RANK() function returns the rank of a numerical value within a list of other numerical values and uses the following syntax:
RANK(number, ref, [order])
An explanation of the three arguments follows:
- Number is required and identifies the number you want to rank.
- Ref is required and references a range of cells that contain the numerical values related to number.
- Order is the only optional argument and identifies how to rank number:
- 0 is the default and can be omitted: ranks in descending order.
- Any nonzero numerical value ranks ref as if is sorted in ascending order.
Now that you know the basics, you should be aware of a few interesting behaviors.
When ranking a set of values, you need to know how RANK() handles duplicate numbers: RANK() returns the same ranking value for each. That, for better or worse, affects the ranking of subsequent numbers because Word’s RANK() won’t skip a returned rank value. For example, in an ascending list, if the number 120 occurs twice and the rank of both is 8, the next ranking value will return 10, not 9. This ranking behavior makes perfect sense, but it might throw you a bit when you view the results. You can add a correction factor, but it isn’t part of the RANK() function itself, so I won’t introduce it here.
Now, let’s look at the simple list of article titles and the number of times each article was viewed shown in Figure A. (The numbers were generated by a RANDBETWEEN() function and are not true values.) To return the rank of each number of views within the same list, enter the following in D3:
=RANK([@Views],[Views],0)
If you’re not working with a Table object, use the following:
=RANK(D3,D3:D42)
Figure A
To get the best viewing experience, sort by the ranking values in column D, as shown in Figure B.
Figure B
Remember, in ranking terms, the lower the value the higher the rank. For instance, the article How to spell page numbers instead of using digits in a page-numbering scheme had the greatest number of views, 5,963, and ranks first.
There are no duplicates in the current list, so let’s modify a couple of the view numbers. Figure C shows the new results.
Figure C
The titles in rows 5 and 6 are tied for third place. The titles in rows 9 and 10 are tied for seventh place. And true to our earlier discussion the ranks of fourth and eighth aren’t assigned, visually.
Now, let’s move on to the newer ranking functions.
How to use Excel’s RANK.AVG() function
In almost every way, Excel’s RANK.AVG() function performs as RANK(), except in one way: RANK.AGE() returns an average rank if a number occurs more than once. The syntax is exactly the same, so enter the following function in E3 to see how it compares to the results of the older RANK() function:
=RANK.AVG([@Views],[Views])
As you can see in Figure D, the returned rank for the first tie is 3.5—the average of the two ranking values. If there is one duplicate, meaning two values, this function will add .5 to the rank.
Figure D
If there are two duplicates, meaning three values, RANK.AVG() rounds up to the next rank and removes the ranks before and after. Figure E shows an example of this in rows 4 through 8.
Figure E
The value 5,604 occurs three times, so the average rank, 3.5 is rounded to 4. Notice that the ranks of 2 and 5 are missing above and below the three occurrences of 5,604.
An average rank might not be what you need, so let’s review the last of Excel’s ranking function, RANK.EQ().
How to use Excel’s RANK.EQ()
Excel’s RANK.AVG() corrects for duplicate values by returning the average rank. Suppose you don’t want the average, but rather, the top rank of the repeated values—similar to RANK(). In fact, RANK.EQ() is RANK(). With the addition of RANK.AVG(), the name RANK.EQ() is more consistent in structure than the earlier-named RANK().
RANK.EQ() uses the same syntax, so let’s go straight to an example. As you can see in Figure F, this function returns the same ranking values as RANK()by assigning the same rank for duplicate numbers.
Figure F
RANK() is still around for backward compatibility only. You don’t need to update existing RANK() functions (at least not yet) but use RANK.EQ() going forward.
Stayed tuned
In our simple example, the data structure of the Excel data simplifies our ranking task because there are no conditions. In a future article, I’ll show you how to handle ranking when a condition must be evaluated.
