How to calculate conditional rank in Excel

Straightforward ratings are easily obtained using one of Microsoft Excel’s rating functions. Calculating conditional ratings is even easier if you let the Excel PivotTable do all the work.

Rank data is very common when you want to show the relationship of one value to other values ​​in a group. You will use the ranking functions of Microsoft Excel to rank scores, sports statics, sales, etc. It is a common task and, thanks to the ranking functions of Excel, an easy task. However, adding a condition to the rankings can make you scratch your head because Excel does not provide the RANKIF() function. If you try searching for solutions on the internet, you will find many complex expressions that are unnecessarily difficult to maintain. Instead, I show you how to return a conditional or grouped rating 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 include presentation file for both .xlsx and .xls . formatsbut only RANK() will work in versions earlier than Excel 2010. Excel for the web supports all three ranking functions.

If you are not familiar with the three Excel ranking functions, consider reading How to calculate rank in Excel sheet.

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

The Excel RANK.EQ() function returns the rank of a numeric value in a list of other numeric values ​​and uses the following syntax:

RANK (number, referral, [order])

The first argument, number, is required and defines the value you want to rank for, it is required to have a reference, and a reference to the reference range you are comparing numbers and ordinal is optional and defines how number ratings.

Simple sheet in Picture A there is a duplicate value — you probably won’t find duplicates in this data type, but I’ve tied a value so you can see how RANK.EQ() handles duplicates. You can tell it’s an array function because there’s only one function (H3) and Excel applies a blue border around the result to spill put. Additionally, the function uses a structured reference because the original data is formatted as a Table object.

Picture A

I’ve included this because we won’t be using it to calculate conditional ratings. Instead, we’ll let an Excel PivotTable handle the calculation. I mention this so you don’t spend a lot of time trying to write a complicated expression unnecessarily.

How to add conditions for ranking using a PivotTable in Excel

A simple rank is easy to calculate, but sometimes you’ll want to meet a condition. For example, using performance data, you might want to calculate a rating for each person, rather than an overall rating for each commission. In this case, you’re actually ranking a group — one person at a time. Condition is human.

First, insert the Excel PivotTable into the same worksheet 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 box, click Existing Sheet.
5. Click inside the Location control and type J2 (Figure BUT) and click OK. Doing so will position the top left corner of the (empty) PivotTable frame at cell J2.

Figure BUT

If Excel does not display the PivotTable Fields pane, right-click the empty pane and select PivotTable Fields from the resulting submenu. It’s time to build a PivotTable:

1. Drag the Personnel field from the top list onto the Rows control.
2. Drag the Commission field from the top list to the Value control, twice. (Yes, I actually said it twice.) Doing so adds a field to the Columns list. Don’t worry about it.
3. Click the second drop-down menu of the Total Commission field in the Value control and select Value Field Settings from the list.
4. Name the new field Charts.
5. Click the Show Values ​​As tab.
6. From the list, select Rank From Smallest to Largest (SIZE), and click OK.

SIZE

Visualization

Visualization display the resulting PivotTable. As you can see, the Rating column that you just added will show the rank for each person where each person is a group. There are six people in the original dataset, so there are six records and a rating from 1 to 6. What you lose is the duplicate rank you saw earlier (Figure BUT), but it’s not relevant. If two of the people have the same total commission, then the Excel RANK() function evaluates those as a tie.

Also, since I chose smallest to largest, you will notice that the rating 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 personnel where 1 represents the highest rank, use the Rank Largest to Smallest option (SIZE).

It’s easy to get straight rankings using one of Excel’s rating functions. It’s easier to get conditional ratings using an Excel PivotTable.

Keep stable

If you can’t sort the data initially, it can be difficult to find the highest ranking commissions. In an upcoming article, I’ll show you how to use Excel’s conditional formatting feature to highlight the top three ranking values.