By assigning the same ranks to multiple identical entries, the RANK.EQ function skips numbers in the overall sequence. In order to avoid this skipping, use the SUMPRODUCT function with the COUNTIF function. This combination ranks duplicate values without skipping the numbers.
Different Rank Functions in Excel
Before we take a closer look at its implementation, let’s briefly review its other two forms found in Excel; RANK.AVG and RANK.
In earlier versions of Excel, only the RANK function was available. However, since Excel 2010, two additional rank functions have been added, namely, RANK.AVG and RANK.EQ. Let’s explore their differences, as you’ll likely see them all in Excel.
RANK.EQ Function: RANK.EQ is a new and improved version of the RANK function and is supported in the latest versions of Excel. Both the functions, however, are the same and serve the same purpose. Whatever you use, it gives the same results.
RANK Function: The RANK function, on the other hand, is available for compatibility with Excel 2007 and earlier and may be removed in any of the new excel versions. Today everyone uses the most recent excel versions, so always use the RANK.EQ function when you have to share sheets with others.
RANK. AVG: When any rank sequence contains the duplicates, RANK.AVG function assigns an average rank to each set of duplicates. Therefore, it serves the same purpose of ranking items, but its primary aim is to catch duplicates.
As this article deals with calculating the ranks for items list, let’s see how RANK.EQ function will rank them. In addition, you will see its use in combination with the COUNTIF function.
Before implementing the formula, let’s have a brief overview of the syntax of the RANK.EQ function.
An Overview of Excel’s Rank Function
So, there are three arguments in RANK.EQ function; number, ref, and order. The first two arguments are required, while the third is optional. To execute RANK.EQ on an array of numbers, you must always specify the first two arguments.
Let’s have a look at the function of each argument:
Number: This argument refers to the number whose rank you want to know. Ref: Ref argument refers to an array of lists of numbers in the sequence. Keep in mind that the ref ignores any non-numeric values found in the array. Order: Despite being an optional argument, you can specify the RANK function to rank items either in descending or ascending order by choosing 0 or 1 in this argument. By default, if you leave it out, it will rank items in descending order.
Excel Rank Function Example
So, now that you understand the arguments of the RANK.EQ function, let’s put it into practice on a dataset to see how it works.
As an example, take a look at the sample data containing percentages of ten students. You can use Rank.EQ function to quickly calculate the ranks based on the percentages of these students. This eliminates the need to calculate them manually.
However, it skips numbers when duplicates are involved, so you may have to use other functions discussed later in the article. Below is the list of students with their final percentages.
The first argument is the number you want to rank, which is the percentage of James Walker and other students down the row for this example. So, select cell B2 as the first argument.
The reference range, in this case, will be from cells B2 to B11, and let’s choose ascending order in order argument. Below you can see the implementation of RANK.EQ Function. The formula looks like this:
Pressing Enter will execute the formula, and you’ll get the rank for James Walker.
In this example, James Walker ranks 5th in the class based on his percentage, which means his percentage is 5th highest compared to the rest of the numbers in an array.
Using RANK Function in Combination With COUNTIF Function
It is possible to use the RANK.EQ function combined with the COUNTIF function to stop the skipping of numbers, but it will also ignore rank duplication. Although no number will repeat twice, two students having the same number may get different ranks.
To understand this better, let’s see how RANK.EQ works together with COUNTIF. The formula looks like this:
Implementing this formula will solve the skipping numbers problem.
There is no duplication in the above ranks. But, James Walker and Gillian Tillman, who were both supposed to get the same rank, are now ranked differently.
Thus, using RANK.EQ with COUNTIF has solved half of the problem, but it fails to generate the desired result.
Using SUMPRODUCT Function With COUNTIF Function
To rank students in a list by assigning the same ranks for equivalent percentages without skipping any number, you can use the SUMPRODUCT function with COUNTIF.
Take a look at the formula below:
The formula may seem complicated, but it’s the best way to rank items correctly. In this way, you can achieve your desired results with rank duplication and no number skipping.
While making the result for your students, you can directly use the SUMPRODUCT formula as a replacement for the RANK function. For non-duplicative ranking calculations, you can use the RANK.EQ function alone or with the COUNTIF function.
Changing Order of Final Result
On the Data tab, click the Sort and Filter group and choose an ascending order to order the rankings.
Compare the results in three rows side by side to better understand how each method of ranking items works.
Rank Items Easily Using RANK.EQ and SUMPRODUCT Functions
Here is how you can use RANK and SUMPRODUCT functions in combination with the COUNTIF function to rank items, with or without duplications. You can also decide whether it skips any number in the sequence or not.
Depending on the situation, you can use a variety of methods to rank numbers according to a set of given criteria. This is an example of how Excel functions can help you save a lot of time and effort in manual calculations. There is a lot more to explore in order to design professional-looking excel spreadsheets.
