How to calculate conditional rank in Excel

A straight rating result’s simple utilizing considered one of Microsoft Excel’s rating features. Calculating a conditional rank is even simpler in the event you let an Excel PivotTable do all of the work.

A blank Excel spreadsheet
Image: PixieMe/Shutterstock

Ranking information is widespread while you wish to characterize the connection of 1 worth to others inside a bunch. You’ll use Microsoft Excel’s rating features to rank grades, sports activities statics, gross sales and so forth. It’s a standard job, and because of Excel’s rating features, a straightforward job. However, including a situation to the rank would possibly go away you scratching your head as a result of Excel doesn’t provide a RANKIF() operate. If you attempt searching for options on the web, you’ll discover many convoluted expressions which are unnecessarily tough to take care of. Instead, I’ll present you return a conditional, or grouped, rating utilizing an Excel PivotTable.

SEE: Software Installation Policy (TechRepublic Premium)

I’m utilizing Microsoft 365 on a Windows 10 64-bit system. You can use Excel’s RANK() in any model. RANK.AVG and RANK.EQ() have been added to Excel 2010 (.xlsx). I’m together with demonstration recordsdata for each the .xlsx and .xls codecs, however solely RANK() will work in variations sooner than Excel 2010. Excel for the online helps all three rating features.

If you’re unfamiliar with Excel’s three rating features, think about studying How to calculate rank in an Excel sheet.

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

Excel’s RANK.EQ() operate returns the rank of a numerical worth inside a listing of different numerical values and makes use of the next syntax:

RANK(quantity, ref, [order])

The first argument, quantity, is required and identifies the worth you wish to rank, ref is required and references the vary of references you’re evaluating quantity to, and order is optionally available and identifies rank quantity.

The easy sheet in Figure A has a replica worth—you in all probability wouldn’t discover duplicate values in this sort of information, however I compelled one so you may see how RANK.EQ() offers with duplicates. You can inform that it’s an array operate as a result of there’s just one operate (H3) and Excel applies a blue border across the ensuing spill set. In addition, the operate makes use of structured referencing as a result of the unique information is formatted as a Table object.

Figure A

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

I included this part as a result of we gained’t use it to calculate a conditional rating in any respect. Instead, we’ll let an Excel PivotTable deal with the calculating. I point out this so that you don’t waste lots of time making an attempt to jot down a posh expression unnecessarily.

How so as to add a situation to rank utilizing a PivotTable in Excel

A easy rank is simple to calculate, however generally you’ll wish to accommodate a situation. For occasion, utilizing the demonstration information, you would possibly wish to calculate the rank for every particular person, as a substitute of an total rank for every fee. In this case, you’re really rating a bunch—every particular person. The situation is the particular person.

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

  1. Click anyplace contained in the Commissions Table (B2:F13).
  2. Click the Insert tab.
  3. In the Tables group, click on PivotTable.
  4. In the ensuing dialog, click on Existing Worksheet.
  5. Click contained in the Location management and enter J2 (Figure B) and click on OK. Doing so will find the top-left nook of the PivotTable body (empty) at cell J2.

Figure B

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

If Excel doesn’t show the PivotTable Fields pane, right-click the empty body and select PivotTable Fields from the ensuing submenu. It’s time to construct the PivotTable:

  1. Drag the Personnel subject from the highest checklist to the Rows management.
  2. Drag the Commission subject from the highest checklist to the Values management, twice. (Yes, I actually mentioned twice.) Doing so will add a subject to the Columns checklist. Don’t fear about it.
  3. Click the second Sum of Commissions subject’s dropdown within the Values management and select Value Field Settings from the checklist.
  4. Name the brand new subject Rankings.
  5. Click the Show Values As tab.
  6. From the checklist, select Rank From Smallest to Largest (Figure C), and click on OK.

Figure C

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

Figure D

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

Figure D exhibits the ensuing PivotTable. As you may see, the Ranking column that you simply simply added shows the rank for every particular person the place every particular person is a bunch. There are six folks within the unique information set, so there are six data and a rank of 1 to six. The one factor you lose is the duplicate rank you noticed earlier (Figure B), nevertheless it’s irrelevant. If two of the folks had the identical whole commissions, Excel’s RANK() would consider these as a tie.

In addition, as a result of I selected smallest to largest, you’ll discover that the rating values are reversed from what you would possibly anticipate: 1 represents the bottom rank. You ought to know that each methods can be found to you. To rank the personnel the place 1 represents the very best rank, use the Rank Largest to Smallest choice (Figure C).

It’s simple to get a straight rating utilizing considered one of Excel’s rating features. It’s simpler to get a conditional rating utilizing an Excel PivotTable.

Stay tuned

If you may’t kind the unique information, it may be tough to search out the top-ranking commissions. In a future article, I’ll present you use Excel’s conditional formatting characteristic to focus on the highest three rating values.

Source hyperlink

Leave a Reply

Your email address will not be published.