The result of direct ranking is easy with one of the Microsoft Excel ranking functions. Calculating a conditional rank is even easier if you let an Excel spreadsheet do all the work.
Ranking data is common when you want to represent the relationship of one value to another within a group. You will use the Microsoft Excel ranking functions to rank scores, sports statistics, sales, etc. This is a common task and, thanks to Excel’s ranking features, an easy task. However, adding a condition to the rank can make you scratch because Excel does not offer the RANKIF () function. If you try to find solutions on the Internet, you will find a lot of complicated expressions that are unnecessarily difficult to maintain. Instead, I’ll show you how to restore a conditional or grouped ranking using a summary Excel spreadsheet.
LOOK: Software installation policy (TechRepublic Premium)
I am using Microsoft 365 on Windows 10 64-bit system. You can use RANK () on Excel in any version. RANK.AVG and RANK.EQ () were added to Excel 2010 (.xlsx). I include demo files for both .xlsx and .xls formats, but only RANK () will work in versions older than Excel 2010. Excel for Web supports all three ranking features.
If you are unfamiliar with the three features of Excel ranking, consider reading How to Calculate Rank in an Excel Sheet.
What is the RANK.EQ () function of Excel?
Excel’s RANK.EQ () function returns the rank of a numeric value in a list of other numeric values and uses the following syntax:
RANK (number, ref., [order])
The first argument, the number, is mandatory and identifies the value you want to classify, ref is mandatory and refers to the range of references you compare the number with, and the order is optional and identifies how to classify the number.
The simple sheet in Figure A has a duplicate value – you probably won’t find duplicate values in this data type, but I forced one so you can see how RANK.EQ () handles duplicates. You can say that this is an array function because there is only one function (H3) and Excel applies a blue frame around the resulting spill set. In addition, the function uses structured reference, as the original data is formatted as a table object.
I included this section because we will not use it at all to calculate conditional rankings. Instead, we’ll leave a summary table of Excel to handle the calculation. I mention this so that you don’t waste a lot of time trying to write an unnecessarily complicated expression.
How to add a ranking condition using a summary table in Excel
A simple rank is easy to calculate, but sometimes you will want to comply with a condition. For example, using the demo data, you may want to calculate the rank for each person instead of the overall rank for each committee. In this case, you are actually ranking a group – each person. The condition is man.
First, let’s insert an Excel summary table into the same worksheet as follows:
- Click somewhere in the commission table (B2: F13).
- Click the Insert tab.
- In the Tables group, click Summary Table.
- In the resulting dialog box, click Existing Worksheet.
- Click inside the location control and enter J2 (Figure B) and click OK. This way you will find the upper left corner of the summary table frame (empty) in cell J2.
If Excel does not display the PivotTable Fields panel, right-click the empty frame and select PivotTable Fields from the resulting submenu. It’s time to create the summary table:
- Drag the Staff box from the list above to the Rows control.
- Drag the Commission field from the list above to the Values control twice. (Yes, I did say that twice.) This will add a field to the list of columns. Don’t worry about it.
- Click the drop-down menu in the second Commission Amount field in the Values control and select Value field settings from the list.
- Name the new field Ranking.
- Click the Show Values As tab.
- From the list, select Rank from lowest to highest (Figure C) and click OK.
Figure D shows the resulting summary table. As you can see, the ranking column you just added shows 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 rank from 1 to 6. The only thing you lose is the duplicate rank you saw earlier (Figure B), but it does not matter. If two people have the same total commissions, Excel’s RANK () will rate them as a draw.
Also, since I chose the smallest to the largest, you will notice that the ranking values are reversed from what you might expect: 1 represents the lowest rank. You need to know that both methods are available to you. To rank staff where 1 represents the highest rank, use the highest to lowest rank option (Figure C).
It’s easy to get direct ranking using one of Excel’s ranking features. It is easier to get a conditional ranking using a summary Excel spreadsheet.
Stay on the line
If you can’t sort the original data, it can be difficult to find the highest ranked commissions. In a future article, I’ll show you how to use the Excel conditional formatting feature to highlight the first three ranking values.