USING FORMULAS: USING INDEX NUMBERS IN A CALCULATED ITEM in Font

Generator PDF-417 2d barcode in Font USING FORMULAS: USING INDEX NUMBERS IN A CALCULATED ITEM

The items in your field change frequently, and you d like to refer to the items by number in the formulas instead of by name.

You can use an index number to refer to field items. For example, to multiply the data for the first item in the Date field by 5 percent, use the following formula: =Date[1]*.05

Caution If you create a calculated item that refers to items by position, the Top 10 AutoShow and

You can refer to items by their position relative to the calculated item. For example, to calculate the difference between the data for the first visible Date item and the last visible Date item, use the following formula: =Date[1]-Date[-1] This formula assumes that the calculated item will be last in its field and the last date is directly above it (see Figure 3-17).

Caution If you create a calculated item that refers to items by relative position, the Top 10 AutoShow

You can select a cell that shows the result of a calculated item, and modify its formula in the formula bar. For example, in the previous calculated item example, the index number was used to calculate the difference between the first visible item and the last: =Date[1]-Date[-1] If one of the columns has a blank cell in the first position, you can modify the formula to refer to the second item: =Date[2]-Date[-1]

Unlike worksheet formulas, where you can select several cells, make a change, then press Ctrl + Tip Enter to apply the change to all selected cells, the pivot table cells with calculated item formulas must be changed individually.

Your pivot table contains a Total field that stores the total amount of each sale, and a Units field, that stores the number of units sold. You d like a Bonus field that calculates the bonus payable if a sales representative has sold more than 100 units of any product.

1. In the pivot table, select any cell. 2. On the Pivot toolbar, choose PivotTable Formulas Calculated Field. 3. Type a name for the calculated field, for example, Bonus. 4. Press the Tab key to move to the Formula box. 5. Type the formula =IF(Units>100,Total*0.03,0) 6. Click the OK button to save the calculated field and close the dialog box.

Note You can t change the summary function for a calculated field. Sum is the only function available for

You can go back into the Formula dialog box, and modify the settings. 1. In the pivot table, select any cell. 2. On the Pivot toolbar, choose PivotTable Formulas Calculated Field. 3. From the Name dropdown, select the name of the calculated field that you want to modify. 4. In the Formula box, change the formula. 5. Click the Modify button, then click OK to close the dialog box.

You can hide a calculated field, then show it again later: 1. Click the dropdown arrow in the Data field list. 2. Remove the checkmark from the calculated field, then click OK.

1. In the pivot table, select any cell. 2. On the Pivot toolbar, choose PivotTable Formulas Calculated Field.

3. From the Name dropdown, select the name of the calculated field that you want to delete. 4. Click the Delete button, then click OK to close the dialog box.

If it s a calculated field, you ll see its name in the PivotTable Field List. If the name doesn t appear there, it s a calculated item.

You want to add a calculated item in a field, but some items are grouped, and you get an error message.

In your pivot table you have a Plan amount and an Actual amount. You d like to calculate the percent difference between Actual and Plan.