How to calculate conditional rank in Excel

A straight ranking result is easy using one of Microsoft Excel’s ranking functions. Calculating a conditional rank is even easier if you let an Excel PivotTable do all the work.

Image: PixieMe/Shutterstock

Ranking data is common when you want to represent the relationship of one value to others within a group. You’ll use Microsoft Excel’s ranking functions to rank grades, sports statics, sales and so on. It’s a common task, and thanks to Excel’s ranking functions, an easy task. However, adding a condition to the rank might leave you scratching your head because Excel doesn’t offer a RANKIF() function. If you try looking for solutions on the internet, you’ll find many convoluted expressions that are unnecessarily difficult to maintain. Instead, I’ll show you how to return a conditional, or grouped, ranking using an Excel PivotTable.

SEE: Software Installation Policy (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.

If you’re unfamiliar with Excel’s three ranking functions, consider reading How to calculate rank in an Excel sheet.

What is Excel’s RANK.EQ() function?

Excel’s RANK.EQ() function returns the rank of a numerical value within a list of other numerical values and uses the following syntax:

RANK(number, ref, [order])

The first argument, number, is required and identifies the value you want to rank, ref is required and references the range of references you’re comparing number to, and order is optional and identifies how to rank number.

The simple sheet in Figure A has a duplicate value—you probably wouldn’t find duplicate values in this type of data, but I forced one so you can see how RANK.EQ() deals with duplicates. You can tell that it’s an array function because there’s only one function (H3) and Excel applies a blue border around the resulting spill set. In addition, the function uses structured referencing because the original data is formatted as a Table object.

Figure A

RANK.EQ() returns the same rank for duplicate values.
RANK.EQ() returns the same rank for duplicate values.

I included this section because we won’t use it to calculate a conditional ranking at all. Instead, we’ll let an Excel PivotTable handle the calculating. I mention this so you don’t waste a lot of time trying to write a complex expression unnecessarily.

How to add a condition to rank using a PivotTable in Excel

A simple rank is easy to calculate, but sometimes you’ll want to accommodate a condition. For instance, using the demonstration data, you might want to calculate the rank for each person, instead of an overall rank for each commission. In this case, you’re actually ranking a group—each person. The condition is the person.

First, let’s insert an Excel PivotTable into the same sheet as follows:

  1. Click anywhere inside the Commissions Table (B2:F13).
  2. Click the Insert tab.
  3. In the Tables group, click PivotTable.
  4. In the resulting dialog, click Existing Worksheet.
  5. Click inside the Location control and enter J2 (Figure B) and click OK. Doing so will locate the top-left corner of the PivotTable frame (empty) at cell J2.

Figure B

Set the PivotTable object’s references.
Set the PivotTable object’s references.

If Excel doesn’t display the PivotTable Fields pane, right-click the empty frame and choose PivotTable Fields from the resulting submenu. It’s time to build the PivotTable:

  1. Drag the Personnel field from the top list to the Rows control.
  2. Drag the Commission field from the top list to the Values control, twice. (Yes, I really said twice.) Doing so will add a field to the Columns list. Don’t worry about it.
  3. Click the second Sum of Commissions field’s dropdown in the Values control and choose Value Field Settings from the list.
  4. Name the new field Rankings.
  5. Click the Show Values As tab.
  6. From the list, choose Rank From Smallest to Largest (Figure C), and click OK.

Figure C

Add a ranking column to the PivotTable.
Add a ranking column to the PivotTable.

Figure D

The resulting PivotTable ranks each person in relation to the others.
The resulting PivotTable ranks each person in relation to the others.

Figure D shows the resulting PivotTable. As you can see, the Ranking column that you just added displays the rank for each person where each person is a group. There are six people in the original data set, so there are six records and a rank of 1 to 6. The one thing you lose is the duplicate rank you saw earlier (Figure B), but it’s irrelevant. If two of the people had the same total commissions, Excel’s RANK() would evaluate those as a tie.

In addition, because I chose smallest to largest, you’ll notice that the ranking values are reversed from what you might expect: 1 represents the lowest rank. You should know that both ways are available to you. To rank the personnel where 1 represents the highest rank, use the Rank Largest to Smallest option (Figure C).

It’s easy to get a straight ranking using one of Excel’s ranking functions. It’s easier to get a conditional ranking using an Excel PivotTable.

Stay tuned

If you can’t sort the original data, it might be difficult to find the top-ranking commissions. In a future article, I’ll show you how to use Excel’s conditional formatting feature to highlight the top three ranking values.

By Julio MarchiPosted on