Pivot table: Custom "Summarize by" functions

[Guest]

Is there any way to have custom ways of summarizing data in a pivot table?

I have some grouped values etc, and I want the average for each group and
the MAX of the group minus the MIN of the group.

It seems that you can run a formula on each individual row that gets entered
(though I haven't figured out how), and you can run a formula on the SUM of a
certain group of data, but you don't seem to be able to, say, subtract the
MIN from the MAX of a group of data. The summary functions listed include
SUM, COUNT, VAR, MAX, MIN, etc, but I can't see any way of combining these.

When I enter a formula and do Column1 * 5, it gives me the sum of each
Column1 group * 5, when I do COUNT(Column1) it gives me 1, etc. I want
MAX(Column1) - MIN(Column1) to give me the max of the group minus the min of
the group.

Does anyone know the most elegant way to do this?

Thanks a lot,
Kestas

 

[Roger Govier]

Hi

Add your Value field to the Data area 3 times,
1 with the setting of Average,
2 with the setting of Max and
3 with the setting of Min
Drag the Data button on the PT and drop it on Total, and you will see
the 3 values side by side
Average Max Min
Group1
Group2

To get your difference between Max and Min I think you will need to do
the calculation outside of the PT in a column to the right, using the
GETPIVOTDATA function. In my case, the formula looked like this

=GETPIVOTDATA("Max of Value2",$F$2,"Category",F4)-
GETPIVOTDATA("Min of Value2",$F$2,"Category",F4)

Pointing to the various cells on the PT and letting Excel create the
formula, it puts the actual Category name or Number where I show F4 in
the formula. I changed it to the cell location of the Category number,
which then allowed it to be copied down.

If you need more help on GETPIVOTDATA (and indeed on PT's) then go to
Debar Dalgleish's site
http://www.contextures.com/xlPivot06.html