How to calculate conditional rank in Excel

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

Ranking data is common when you want to show the relationship of one value to others within a group. You use Microsoft Excel’s ranking functions to rank numbers, sports statistics, sales, and so on. It’s a common task and an easy task thanks to Excel’s ranking features. However, adding a condition to the rank may leave you scratching your head because Excel does not provide a RANKIF() function. If you search for solutions on the internet, you will find many complicated expressions that are unnecessarily difficult to maintain. Instead, I’ll show you how to return a conditional or grouped rank using an Excel pivot table.

SEE: Software Installation Policy (Tech Republic Premium)

I use Microsoft 365 on a Windows 10 64-bit system. You can use Excel’s RANK() in any version. RANK.AVG and RANK.EQ() have been added to Excel 2010 (.xlsx). I participate demonstration files for both .xlsx and .xls formatsbut only RANK() works in versions earlier than Excel 2010. Excel for the web supports all three rank functions.

If you’re unfamiliar with Excel’s three ranking features, 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 numeric value within a list of other numeric values, using 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 refers to the range of references you are comparing number to, and order is optional and identifies how to rank number.

The simple sheet in Image 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() handles duplicate values. You can tell it’s an array function because there’s only one function (H3) and Excel has a blue border around the resulting one to spill set. In addition, the function uses structured references because the original data is formatted as a table object.

Image A

I’ve included this section because we won’t be using it to calculate a conditional rank at all. Instead, let’s let an Excel pivot table handle the calculation. I mention this so that you don’t waste a lot of time trying to write a complex expression unnecessarily.

Add a condition to rank using a pivot table in Excel

A simple ranking is easy to calculate, but sometimes you want to accommodate a condition. For example, if you’re using the demonstration data, you might want to calculate the rank for each person, rather than an overall rank for each commission. In this case, you’re actually arranging 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 in the Commission table (B2:F13).
2. Click the Insert tab.
3. In the Tables group, click PivotTable.
4. In the resulting dialog box, click Existing Worksheet.
5. Click in the location control and enter J2 (Figure B) and click OK. Doing so displays the upper left corner of the pivot table frame (blank) in cell J2.

Figure B

If Excel does not display the PivotTable Fields pane, right-click the empty frame and choose PivotTable Fields from the resulting submenu. It’s time to create the pivot table:

1. Drag the Staff field from the top list to the Rows control.
2. Drag the Commission field twice from the top list to the Values ​​control. (Yes, I actually said it twice.) Doing this adds a field to the list of columns. Don’t worry about it.
3. Click the drop-down list of the second Sum of Commissions field in the Values ​​control and choose Value Field Settings from the list.
4. Give the new field a name Rankings†
5. Click the Show Values ​​As tab.
6. In the Rank from smallest to largest list, chooseFigure C) and click OK.

Figure C

Figure D

Figure D shows the resulting pivot table. As you can see, the Rank column you just added shows the ranking for each person where each person is a group. There are six people in the original dataset, so there are six records and a rank of 1 to 6. All you lose is the double rank you saw before (Figure B), but it’s irrelevant. If two of the people had the same total commissions, Excel’s RANK() would evaluate them as a tie.

Additionally, since I’ve chosen smallest to largest, you’ll notice that the rank values ​​are the reverse of what you’d expect: 1 represents the lowest rank. You should know that both ways are available to you. To rank the staff where 1 represents the highest rank, use the Rank from largest to smallest option (Figure C†

It’s easy to get a straight rank using one of Excel’s rank functions. It’s easier to get a conditional rank using an Excel pivot table.

Stay tuned

If you can’t sort the original data, it can be difficult to find the highest commissions. In a future article, I will show you how to use Excel’s Conditional Formatting feature to highlight the top three rank values.