Defining Calculated Data Fields

Pivot tables offer two types of formulas. The most useful type defines a formula for a calculated field. This adds a new field to the pivot table. Calculations for calculated fields are always done at the summary level. If you define a calculated field for average price as Revenue divided by Quantity, Excel first adds up the total revenue and the total quantity, and then does the division of these totals to get the result. In many cases, this is exactly what you need. If your calculation does not follow the associative law of mathematics, it may not work as you expect.

To set up a calculated field, use the Add method with the CalculatedFields object. You have to specify a field name and a formula. Note that if you create a field called Average Price, the default pivot table produces a field called Sum of Average Price. This is misleading and downright silly. What you have is actually the average of the sums of prices. The solution is to use the Name property when defining the data field to replace Sum of Average Price with

0 0

Post a comment