In recent blog posts, we’ve looked at the pivot table Count function and the Average function. Now we’ll look at two more functions, that are closely related — Min and Max.
Max Summary Function
The Max summary function shows the maximum value from the underlying values in the Values area. The result is the same as using the MAX function on the worksheet to calculate the maximum of the values.
In the screen shot below, you can see the source data for a small pivot table, and the maximum quantity, using the worksheet’s MAX function, is 97.
With a pivot table, you can quickly see the maximum for each product that was sold, and the grand total — 97 — which matches the worksheet maximum.
Min Summary Function
The Min summary function shows the minimum value from the underlying values in the Values area. The result is the same as using the MIN function on the worksheet to calculate the minimum of the values.
In the screen shot below, you can see the source data for a small pivot table, and the minimum quantity, using the worksheet’s MIN function, is 8.
With a pivot table, you can quickly see the minimum for each product that was sold, and the grand total — 8 — which matches the worksheet minimum.
In both the worksheet and the pivot table, the blank cell is ignored when calculating the minimum amount.
___________
___________
How to do this when you have a pivot table that keeps wanting to give you a sum-of-values rather than the Mx for that top-10 group?