Calculated Item Formulas

A calculated item formula defines calculations using the contents of items in a PivotTable.

The only names calculated item formula shall use are names of fields and items defined within the PivotTable the formula is used for.

A calculated item formula has the following form:

 pivot-item-expression ;
 pivot-item-expression= {space}, pivot-nospace-item-expression, {space} ;
 "(", pivot-item-expression, ")" | 
 pivot-constant | 
 prefix-operator, pivot-item-expression | 
 pivot-item-expression, pivot-infix-operator, pivot-item-expression | 
 pivot-item-expression, postfix-operator | 
 pivot-item-function-call | 
 pivot-items ;
 pivot-function-name, "(", pivot-item-function-argument-list, ")" ;
 [pivot-item-expression], { comma, pivot-item-function-argument-list} ;
 pivot_item, [ space, {space}, pivot-items] ;
 pivot-item-name |
 pivot-field-name, "[", pivot-item-value, "]" ;
 pivot-item-name | 
 [sign], whole-number-part

pivot-item refers to an item either by its name or by position.

pivot-item-name refers to an item by its name.

pivot-field-name [pivot-item-name] refers to an item by its field name and its own name. This notation shall be used to avoid #NAME? errors when two items in two different fields have the same name.

pivot-field-name[whole-number-part] refers to an item by its position in the PivotTable as currently sorted and displayed. The item referred to in this way can change whenever the positions of items change or different items are displayed or hidden. Hidden items are not counted in this index.

pivot-field-name [sign whole-number-part] refers to an item using relative positions. The positions are determined relative to the calculated item that contains the formula.