# Excel RANK

# without Skipping Numbers

# (Top 3 Report with Duplicates)

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?es.

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*; it is not necessary to press

**ENTER****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.

# 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

## Practice Workbook

Feel free to Download the Workbook HERE.

**Excel Dashboards that Inform & Impress**

Peter AthertonApril 15, 2019 at 7:22 pmHi Leila,

A very informative lesson. The Unique Rank I did before and called it Nominal Rank as the unber you got depended on yur position in the list. For those of us that do not have Xl2019 and can not use the JoinText function I wrote this little function (no error checking), This might help some others..

Function getRanks(ByVal Rank As Range, ranks As Range, ColOffset As Integer) As String

Dim c As Range, tmp As String, counter As Integer

For Each c In ranks

If CInt(c) = CInt(Rank) Then

counter = counter + 1

If counter = 1 Then

tmp = c.offset(0, ColOffset)

ElseIf counter > 1 Then

tmp = tmp & “, ” & c.offset(0, ColOffset)

End If

End If

Next c

getRanks = tmp

End Function

I tried to download your tutorials but it failed and the second attempt I got a notice saying that the site may be compromised :(, Hope this helps.

regards

Peter

Bryon SmedleyApril 19, 2019 at 12:58 pmThank you for your custom function; I’m certain it will help many users with the same issue as yourself.

(We will look into your download issue. Thank you for bringing it to our attention.)

The XelPlus Team

MajidJune 15, 2019 at 5:05 amIts great