customize a calculated field in a Pivot table

[Paul James]

I have a pivot table and instead of showing row and colum totals like the
wizard creates automatically, I would like to have those fields calculate
the averages of the values in the Data area. In addition, I need to make it
a customized Average calculation, because I need to include empty cells in
the denominator of the calculation, and not simply a count of the non-empty
cells, the way the Excel Average function does.

However, the online help for calculated fields in pivot tables says that you
cannot use worksheet functions that require cell references as parameters.
If that's the case, is there any way to replace the row and column totals
with the customized averages?

Thanks in advance.

Paul

 

[Peter Atherton]

Hello Paul

If I understand you you want to average a set of cell and
include an empty cell in the divisor. E.g.
A1 = 12, A2 = 23,A3 = empty, A4 = 45

You want the average to read 20 rather than 26.66..

You will have to use a custom function for this. This is a
simplified version of the Excel function. The followng
will give you the results shown above.

Function AvgBlanks(x)
For Each c In x
tot = tot + c.Value
Count = Count + 1
Next c
AvgBlanks = tot / Count
End Function

This has to be placed into the VB Editor for the workbook.
It can then be used like a normal function (in that
workbook). To place it in the VB Editor:

Press F11, choose Insert, Module. Paste the function into
the editor.

You will have to arrange the formulas so that the pivot
table can use them.

Hope this helps
Regards
Peter