Create a Report That Shows Count, Min, Max, Average, Etc.
January 20, 2023 - by Bill Jelen
Problem: Most of the Pivot Table examples shown thus far are for summing revenue. What if I need to find out the average sale by customer or the smallest sale?
Strategy: Pivot tables offer eleven calculation functions in the Value Field Settings dialog.
To use Field Settings, select one numeric cell in the pivot table to make that field the active field. In the Analyze tab of the ribbon, click Field Settings.
You can now choose from the 11 functions.
When you choose Average, the field heading will become “Average of Revenue”. You can edit the custom name in the Value Field Settings dialog to “Average Revenue” or “Average Sale” or any other heading that you would like. Note, however, that you cannot reuse a name already in the pivot table. So, for example, Revenue would not be allowed, but Revenue_ or “Revenue ” or “ Revenue” would be allowed. Those last two include a trailing space and a leading space.
Gotcha: There is no built-in way to create a median for a pivot table. I’ve heard this question a few times.
This article is an excerpt from Power Excel With MrExcel