How to Create, edit or delete an array formula or Pivot Chart

[agarwalm11]

If the abstract functions and custom calculations do not yield the desired results, you can create your own formulas in calculated fields and calculated items. For example, you could add a calculated item in the formula for the sales commission, which could be different for each region. The report Pivot Table automatically includes the commission in the subtotals and grand totals.

Note

• You can not create formulas in a table report or Pivot Chart connected to OLAP data sources.
• For best results in a Pivot Chart report, work in the report associated with Pivot Table in which you can see the individual data values calculated by your formula.

Create a formula

1. Indicate whether you can place a calculated field or calculated item in a field.

• Choose a calculated field when you want to use data from another field in your formula.
• Choose a calculated item when you want your formula that uses data from one or more elements in a specific field.

2. Do one of the following.

To add a calculated field

1. Click the Pivot Table report.
2. In group Tools click Options tabs, Click Forms, Then Calculated field.
3. In the Name, Enter a name for the field.
4. In the Formula, Enter the formula for the field. For use in the formula, data from another field, click on this field in the Champs, Then click Insert Fields. For example, to calculate a 15% commission on each field's value Sales, You can enter = Sales * 15%.
5. Click Add.

To add a calculated field to a field

1. If the elements in the field are grouped in Group, click Options tab, Click Float.
2. Click on the field you want to add the calculated item.
3. In group Tools on Options tab, Click Forms, Then Calculated item.
4. In the Name, Type a name for the calculated item.
5. In the Formula, Enter the formula for the desired item. For use in the formula, the data of an item, click the item in the list Elements, Then click Insert Item (element must come from the same field as the calculated item).
6. Click Add.
7. If you ungroup elements in step 1, merge them, if necessary.

3. For calculated items, you can enter different formulas cell by cell.

If, for example, an element Orange has calculated a formula Oranges * 0.25 for each month, you can set a different formula for June, July and August (eg Oranges * 0.5).

Proceed as follows:

1. Click a cell to which you want to change the formula. To change the formula for several cells, click on other cells while holding down the CTRL key.
2. In the formula bar, make any desired changes to the formula.

4. If you have multiple calculated items or formulas, adjust the order of calculation as follows:

1. Click the Pivot Table report.
2. In group Tools on Options tab, Click Forms, Then Solve Order.
3. Click on a formula, then Mount or Down.
4. Continue until the formulas are displayed in order of calculation desired.

View a list of formulas

To view a list of all forms used in the report of current Pivot Table, follow these steps:

1. Click the Pivot Table report.
2. In group Tools on Options tab, Click Forms, Then List of forms.

[agarwalm11]

Change a formula

1. Indicate if the formula is in a calculated field or a calculated item. If the formula is calculated in an item, indicate whether it is the only one for this element calculated as follows:

1. Click the Pivot Table report.
2. In group Tools on Options tab, Click Forms, Then List of forms.
3. In the list of formulas, find the formula you want to edit in the list identified by Calculated field or under Calculated item.

When there are several formulas for a calculated item, the default formula entered when creating the element is called the calculated item, a name which appears in column B. For other formulas defined for a calculated item, column B contains both the item name calculated and the names of elements found at this intersection.

You can, for example, have a default formula for a calculated item named MyItem and another formula for this element, identified by January sales of MyItem. In the Pivot Table report, the formula would be found in the cell line sales as MyItem and column in January.

2. Do one of the following:

To edit a formula for calculated field

1. Click the Pivot Tablereport.
2. In group Tools on Options tab, Click Forms, Then Calculated field.
3. In the Name, Select the calculated field you want to change the formula.
4. In the Formula, Change the formula.
5. Click Edit.

To edit a single formula for a calculated item

1. Click on the field for the calculated item.
2. In group Tools on Options tab, Click Forms, Then Calculated item.
3. In the Name, Select the calculated item.
4. In the Formula, Change the formula.
5. Click Edit.

To change the formulas to individual cells of a specific component calculated,
If, for example, an element called Orange has calculated a formula Oranges * 0.25 for each month, you can set a different formula for June, July and August (eg Oranges * 0.5).

1. Click a cell to which you want to change the formula. To change the formula for several cells, click on other cells while holding down the CTRL key.
2. In the formula bar, make any desired changes to the formula.

3. If you have multiple calculated items or formulas, adjust the order of calculation, as follows:

1. Click the Pivot Table report.
2. In group Tools on Options tab, Click Forms, Then Solve Order.
3. Click on a formula, then Mount or Down.
4. Continue until the formulas are displayed in order of calculation desired.

Hide or Delete a formula

If you do not delete a formula permanently, you can hide the field or item. To hide a field, drag it off the report.

1. Indicate if the formula is in a calculated field or a calculated item.

Calculated fields appear in the list of fields in the PivotTable. Calculated items appear as elements within other fields.

2. Do one of the following:

To delete a calculated field

1. Click the Pivot Table report.
2. In group Tools on Options tab, Click Forms, Then Calculated field.
3. In the Name, Select the field you want to delete.
4. Then click Remove.

To delete a calculated item

1. Click on the field that contains the item you want to delete.
2. In group Tools on Options tab, Click Forms, Then Calculated item.
3. In the Name, Select the item you want to delete.
4. Then click Remove.