Pivot Ranks Don’t Match RANK()
January 24, 2023 - by Bill Jelen
Problem: I set up a pivot table and showed the values as a rank, using Rank Largest to Smallest. Why is the fourth product assigned a rank of #3?
Strategy: As if there is not enough controversy in the Excel ranking world, Excel came up with yet another way to handle ranking with pivot tables. The issue always centers around any ties and how the subsequent values are numbered.
Typically, if you have two values tied at #2, the next value would be assigned a rank of 4.
Starting in Excel 2010, the
RANK.AVG would assign the tied values a 2.5, and assign the next item a rank of 4.
Pivot tables do something different, assigning both of the tie values a 2, then going to #3 for the next item.
If you need one of the methods shown in E:G, plan on adding a calculation next to your pivot table instead of using the built-in rank.
This article is an excerpt from Power Excel With MrExcel