Calculated Items and Calculated Fields

We have seen that it is possible to add a calculated field to a pivot table. A calculated field is special type of PivotField object that is not part of the original source data, but, instead, is calculated from source fields using a formula. Note that there is no such thing as a CalculatedField object, but there is a CalculatedFields collection.

As we have seen, to create a new calculated field, we use the Add method of the CalculatedFields collection of the PivotTable object. The syntax is:

               CalculatedFieldsObject.Add(Name, Formula)

where Name is the name of the field and Formula is the formula for the field.

On the other hand, a calculated item is a special type of PivotItem object associated with a given PivotField object. (There is no such thing as a CalculatedItem object, but there is a CalculatedItems collection.) The values of this item are calculated by using a formula.

The PivotField object has a CalculatedItems collection of all calculated items for that pivot field. To create a new calculated item, we use the Add method of the CalculatedItems object. This method has the same syntax as the Add method of the CalculatedFields object:

               CalculatedItemsObject.Add(Name, Formula)

where Name is the name of the field and Formula is the formula for the field.

To illustrate, the following code adds a new calculated item to the Store Type pivot field:

ActiveSheet.PivotTables("Sales&Trans"). _
  PivotFields("Store Type").CalculatedItems. _
  Add "CompanyX2", "='Store Type'Company*2"

The results are shown in Figure 20-42, where the calculated item is CompanyX2. The value in each of the CompanyX2 cells is twice the value in the corresponding Company cell.

Figure 20-42. Illustrating a calculated item (CompanyX2)

For comparison, let us add a calculated field to the pivot table in Figure 20-42. We will add the same calculated field that we added when we discussed the CalculatedFields method earlier in the chapter:

With ActiveSheet.PivotTables("Sales&Trans"). _
   CalculatedFields.Add("Average", _
   "= Sales/Transactions")
      .Orientation = xlDataField
      .Name = "Avg Check"
      .NumberFormat = "##.#"
End With

The result is shown in Figure 20-43.

Figure 20-43. Illustrating a calculated field and calculated item

Note that the ListFormulas method of the PivotTable object will produce a list (on a separate worksheet) of all formulas in the pivot table. The outcome for the pivot table in Figure 20-43 is shown in Figure 20-44.

Figure 20-44. The output of ListFormulas

Let us conclude by recalling that the read-only IsCalculated property can be used to determine whether or not a pivot field or pivot item is calculated.