Microsoft Office Tutorials and References
In Depth Information
Figure 20-21. Illustrating a calculated field
where Name is the name of the field and Formula is the formula for the field.
To illustrate, the following code creates a calculated field and displays it in the pivot table from
Figure 20-10 . The results are shown in Figure 20-21 .
With ActiveSheet.PivotTables("Sales&Trans"). _
CalculatedFields.Add("Average", _
"= Sales/Transactions")
.Orientation = xlDataField
.Name = "Avg Check"
.NumberFormat = "##.#"
End With
Figure 20-21. Illustrating a calculated field
We should make a brief remark about the arithmetic of the calculated field. The calculated field is
computed directly from the data in the pivot table. The source data is not involved directly . This is
why we did not specify an aggregate function for the calculated field. (Such a function would have
been ignored.) Thus, for instance, the value in cell D7 is obtained by dividing the value in cell D6
by the value in cell D5.
Finally, we note that the ListFormulas method can be used to create a list of all calculated fields
on a separate worksheet. Errors-related properties
When the DisplayErrorString property is True , the PivotTable displays a custom error string in
cells that contain errors. (The default value is False .) As the Excel help file observes, this
property is particularly useful for suppressing divide-by-zero errors.
Search JabSto ::

Custom Search