My excuse is that I’ve got a heavy cold, and my brain is definitely not firing on all cylinders. I’ve found a problem which ought to have a simple Excel (not VBA) solution, but I can’t work out what it is. Dick, Jon, anybody: please put me out of my misery.

I’m currently revising some documentation for a leading financial OLAP framework (ActivePivot), so I’m much involved with pivot tables. I was toying with one of my own, which summarized Sales data (in Excel, not OLAP) – the usual stuff:

(Yes, it’s old data, and I need to change the dates). Simple enough to have Min and Max of Sales:

But suppose that I want to see the *difference *between Max and Min. Surely there is somewhere to enter the formula ‘=Max – Min’ (or possibly something like ‘=Values.Max – Values.Min’). But where?

It’s not a Calculated Field, since that is essentially a calculated column on the data table. I could, for example, have a field ‘= Sales / Contacts’.

It’s not a Calculated Item, since that aggregates values for dimension members. For example, I could aggregate Jan+Feb+Mar into a Q1 value. I could even do differences, for example, Jul-Dec minus Jan-Jun. However, although the Min and Max *Values *(fields) appear as column headers, they are not a dimension.

I could have formulas that sit outside the pivot table:

- using GETPIVOTDATA on individual cells
- using an array formula to subtract the whole Min array from the whole Max array.

However, these do not, of course, adapt to changes in the pivot table layout, such as filtering member values.

Since an aggregation function could be any function that takes an array of values and returns a single value, we could have more sophisticated derived measures based on the built-in aggregation functions (Max, Average, Stdev, etc).

The OLAP framework mentioned above is of course a much more sophisticated beast. There are ways to calculate additional fields before aggregation in the hypercube (cf. pivot cache), custom aggregation functions within the hypercube, and post-processor functions to calculate additional values. I think the last of these would be the appropriate one here.

Is there a VBA solution? Right now, I haven’t penetrated deep enough into the Byzantine complexities of the pivot table object model. A quick look indicated that *PivotField.function* is an enumeration of the built-in types. So there’s no obvious hook for a custom aggregation function.

Now I’m going away to cough a lot…

### Like this:

Like Loading...

*Related*

The next time I see a meaningful use of calculated fields or items will be the first time. Beyond the most rudimentary stuff, they’re useless.

I would add another column to the source data with this array formula

=MAX(($A$2:$A$51=A2)*($E$2:$E$51))-MIN(IF($A$2:$A$51=A2,($E$2:$E$51),””))

And take the max of that in a new pivot item.

Thanks, Dick.

I’ll take that as confirmation that I’m not missing something in the pivot table facilities. As you say, calculated fields in particular are a bit limited.

Interesting that this was such a simple requirement to state – but presumably hard for the pivot table/cache implementation to do post-aggregation.

The array-formula (ra! ra!) solution works, as you say, and looks a bit better with Table addressing. The assymmetry between the MAX and MIN cases (because of handling the false-case zeroes in the MIN case) is a bit subtle.

Since this is a pre-aggregation solution, we would potentially need a calculated MaxMinDiff column for each of the three dimension columns. In this case it’s Month, but could also be for Region or Sales Rep.

“for each of the three dimension columns. ”

Ooh, that complicates it then. That’s usually when I give up and move the formulas outside of the PT or roll my own PT with array formula. I wish there was a better solution.

This code will add formula to the right of the columns.

Although you would also need further code to remove previous formula when the row fields change and the resulting number of rows in less than previous.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

With Target.DataBodyRange

With .Columns(.Columns.Count)

.Offset(-1, 1).Resize(1, 1) = “Diff”

.Offset(, 1).FormulaR1C1 = “=RC[-1]-RC[-2]”

End With

End With

End Sub