Problem: I set up a pivot table and showed the values as a rank, using the new Excel 2010 Rank Largest to Smallest. Why is the fourth product assigned a rank of #3?
- Why is C7 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.