Average from the GETPIVOTDATA formula

[Guest]

Hello,
I have a pivot table that calculates various data points by cost center, by
year, by month. I want to use the GETPIVOTDATA formula to calculate the
average for a particular data point.
I am receiving a #REF error from the following formula: =GETPIVOTDATA("Sum
of LTL lb/SW",Pivots!$A$10,"Fiscal Year","FY 2007","City","Atlanta","CDC
Market","CDCATL").
Can I specify a particular range within the pivot table? How can I get this
to work?
Thanks,

 

[Roger Govier]

Hi Rob

Rather than using GetpivotData and doing the calculation outside of the
PT, could you not achieve what you want by either adding the data field
twice, and setting the second instance to be Average rather than Sum
or
if you are Grouping on sum field, double click the field heading and
change Subtotalling to Custom, and tick Sum and Average.

If necessary, create a second Pivot from your source data, and drag some
fields to the Page area so you can select just the subset of data in
which you are interested.