Let’s look at Excel’s ranked function. How can you rank your values in descending or ascending order and how can you rank duplicate values without skipping numbers in the sequence?
In our example, we have a list of sales managers and their sales.
We want to create a report that displays the top 3 sales managers based on those numbers.
The problem is that several of the sales managers have the same sales value and will result in a 2-way or 3-way tie for the same rank position.
We want to display each of the names that result in a tie.
Ranking is ideal for grouping your data or bringing order to disordered data. It’s when you want to compare one value against a set of values and determine how that single value ranks in respect to the full set of values.
Example #1 Simple Ranking (in-place)
Using our previously examined list of sales managers and sales, we want to create a ranking of the sales in column C, but we do not wish to sort the original data.
It would be easy to sort the list by sales and then create a “helper column” of numbers that count from 1 forward. The problem is that if we were to copy new values into the data set, the “helper column” would no longer be accurate.
Our solution will be to create in column C a formula using the RANK function.
You will notice when we begin typing the word “rank”, the IntelliSense service provides us with several options. Per Microsoft…
- RANK – This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.
- EQ – Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. If you were to sort the list, the rank of the number would be its position.
- AVG – Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned.
We will use the RANK.EQ function for our solution.
A few notes about the RANK.EQ function:
- This function works identically to the older RANK function.
- If there are duplicates in your data resulting in 2-way or 3-way ties, each of those in the tied group will receive the same rank value. However, the list will then skip the number of following rank positions relative to the number of items in the prior tied group.
Example: If the number 10 were to appear 3 times in a list, and its rank is position #5, the following item(s) in the list would begin in rank position #8. The number 10 would consume slots #5, #6, and #7, even though they will all be labeled as #5.
Let’s select cell C5 and create the following formula:
=RANK.EQ(B5,$B$5:$B$24)
We will take the rank of the value in cell B5 and compare it against all other values in cells B5 through B24. (Remember to press F4 and convert the B5:B24 reference to absolute $B$5:$B$24 so it will not change when the formula is copied down the table.)
You can add an optional argument to the formula to determine the ascending/descending order of the ranks. If you omit this argument, descending order is the default behavior.
The returned value of 2 indicates that James Smith’s sale of 9,000 is the second highest in the list.
If we replicate the formula down the remainder of the table, we see that William Jones’ sale of 9,750 is the top-ranking sale of the set.
“Houston, we have a problem.”
Observe that there are three sales reps with a sale value of 9,000; James Smith (row 5), Michael Brown (row 8), and Maria Martin (row 21).
Notice we are also missing the ranks of #3 and #4.
This is the “slot use” behavior mention earlier. We will lose any number of slots commensurate with the number of “ties” in the list.
Let’s fix this behavior.
Example #2 Rank Duplicates but DON’T Skip Numbers
To ensure that John Johnson (row 6) receives a ranking of #3, we will use a more sophisticated formula, which oddly does not use any of the included RANK functions.
NOTE: We will create this formula and get it working but will hold off on dissecting its logic until later in the tutorial.
Select cell D5 and enter the following formula:
=SUMPRODUCT((B5<=$B$5:$B$24)/COUNTIF($B$5:$B$24,$B$5:$B$24))
Fill the formula down the remainder of the table.
Notice that rows 5, 8, and 21 have a rank of #2 while rows 6 and 10 have a rank of #3; rows 7 and 17 have a rank of #4. We did not skip any rank positions.
Creating the Dashboard Report
Our boss wants a summarized version of the table that displays the Top 3 sales rankings. One of the requirements is that if there is a multi-way tie, all of the sales reps names for that rank must be displayed.
To derive this information, we need to create an array formula. Luckily, it’s a very simple array formula.
On the “Report” sheet, select cell D6 and enter the following formula:
=IF(C6=Data!$D$5:$D$24,Data!$A$5:$A$24,””)
This formula will compare each item in cells D5:D24 and compare it to the rank number in cell C6. If the value in cells D5:D24 matches, return the associated name in cells A5:A24.
When we hit ENTER, we don’t see any answers.
This is because we are returning an array (a list) of answers and we are seeing the first answer in the array.
Our best friend in this situation is the F9 key. Highlight the formula in the Formula Bar and press the F9 key. We will see all the answers returned from the calculation.
Notice that the first answer is “blank”; that’s why there was no visible answer when we hit ENTER.
We see that “William Jones” is the only real answer in the list. What good is the answer if we can’t see the answer in the cell?
REMEMBER: Press the Escape key (ESC) so you do not permanently change your formula.
Solving the “Hidden List” Problem
We will modify the existing formula in cell D6 and use a relatively new function in Excel called TEXTJOIN.
TEXTJOIN is available in Office 2019 and Office 365.
Modify the formula as follows:
=TEXTJOIN(“, “,TRUE,IF(C6=Data!$D$5:$D$24,Data!$A$5:$A$24,””))
The TEXTJOIN function will take all the answers generated by the IF function and concatenate them together into an unbroken list of names.
The TEXTJOIN function combines the text from multiple cells/ranges and includes a delimiter you specify between each text value that will be combined. We will provide a “comma-space” to act as our delimiter.
The TEXTJOIN function also allows us to determine how to handle blank cells/ranges. Since we only want the items in the array that contain names, and not the blank items, we will use the “TRUE” option to invoke this behavior.
Because this is an array formula, we need to press CTRL-SHIFT-ENTER to commit the formula to the cell. If you have one of the most recent versions of Excel that utilizes Dynamic Arrays, you only need to press ENTER; it is not necessary to press CTRL-SHIFT-ENTER.
This produces the following formula (notice the opening and closing braces):
{=TEXTJOIN(“,”,TRUE,IF(C6=Data!$D$5:$D$24,Data!$A$5:$A$24,””))}
If we copy the formula down to cells D7 and D8 we see the following results.
If it helps further your understanding of what is occurring, select cell D7, highlight the IF portion of the formula and press the F9 key.
We see that for the #2 rank position we have 3 names and many blanks. The TEXTJOIN function has ignored all the empty items and merged all the names, separating them with comma-spaces to make the answer more presentable.
Testing the Solution
If we return to the table of sales and change the value of cell B5 to 10,000, the rankings will update.
Returning to the Report sheet, we see that “James Smith” occupies the #1 rank while the other items from the original list have shifted down one level in the ranks.
Explaining the Crazy Formula from Earlier
When you build complex formulas, it’s often a helpful strategy to build the formula in pieces using separate cells or columns. This allows us to focus on solving one problem at a time. Once we have all the problems solved, we can combine all the pieces into a single solution.
It is also helpful to focus on solving the problem for a single item/record in the data and then replicate the result to the remaining items/rows when completed.
Returning to the sheet with our table, select cell F5 and enter the following formula:
=B5<=B5:B24
The result will be “TRUE” because what we are seeing is the first answer in an array of answers. Since the first item compared is B5 to itself, the answer is “TRUE”.
If we highlight the formula and press the F9 key, we see all the TRUE/FALSE responses where the contents of B5 are compared to all the values in cells B5:B24.
Because this is difficult to compare; the TRUE/FALSE responses to the rows in the table, we will perform a little trick to list the TRUE/FALSE responses on the rows next to their respective evaluations.
- Press the ENTER key to preserve the TRUE/FALSE responses in the Formula Bar.
- Highlight cells F5:F24.
- Click in the Formula Bar to reenter edit mode.
- Press CTRL-SHIFT-ENTER.
This will have the effect of cascading the TRUE/FALSE responses down the rows next to their evaluated sales.
The next step to examine is the use of the COUNTIF function. Returning to our “work on one piece at a time” strategy, select cell G5 and enter the following formula.
=COUNTIF(B5:B24,B5:B24)
If we highlight the finished formula and press the F9 key, we see the following list of values.
Using the same trick as before,
- Press the ENTER key to preserve the list of values responses in the Formula Bar.
- Highlight cells G5:G24.
- Click in the Formula Bar to reenter edit mode.
- Press CTRL-SHIFT-ENTER.
What each number is telling us is how many times the current row’s sales occurs in the list. Example: The number 9,000 occurs 3 times in the list, and each time it occurs we encounter a number 3 as the answer for that row.
In the final combined calculation, the results of the COUNTIF function remain the same for each use throughout the table, but the list of TRUE/FALSE responses from the IF will be different for each row in the table.
The next step is to divide each TRUE/FALSE in column F by the value in column G.
Whenever Excel uses a TRUE or FALSE as part of a mathematical operation, TRUE is interpreted as a 1 while False is interpreted as a 0 (zero).
Looking at the first evaluation a TRUE (1) divided by a 3 yields 0.333333.
When we fill the formula down the list, we see the following results.
The sum of all the results is 2.
Finally, why are we using the SUMPRODUCT function? The SUMPRODUCT function is used as a container to store all the intermediate calculations and then add them all together for the result.
A bonus of the SUMPRODUCT is that it does not require the use of a CTRL-SHIFT-ENTER. SUMPRODUCTs can handle arrays natively without any special instructions during execution.
Bonus Formula
If you need a list of completely unique ranks, where ties are spread across rank positions (i.e. a 3-way tie for 2nd place would occupy rank positions 2, 3, and 4), click in cell E5 and enter the following formula.
=RANK.EQ(B5,$B$5:$B$24)+COUNTIF($B$5:B5,B5)-1
The result would be as follows:
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
