Microsoft Office Tutorials and References

In Depth Information

**Creating a Calculated Field or Calculated Item**

The formulas that you develop can also use worksheet functions, but the functions can’t

refer to cells or named ranges.

Inserting a calculated item

The preceding section describes how to create a calculated field. Excel also enables you to create

a
calculated item
for a pivot table field. Keep in mind that a calculated field can be an alternative

to adding a new field to your data source. A calculated item, on the other hand, is an alternative

to adding new rows to the data source — rows that contain formulas that refer to other rows.

In this example, you create four calculated items. Each item represents the commission earned on

the quarter’s sales, according to the following schedule:

h
Quarter 1:
10% of January, February, and March sales

h
Quarter 2:
11% of April, May, and June sales

h
Quarter 3:
12% of July, August, and September sales

h
Quarter 4:
12.5% of October, November, and December sales

Modifying the source data to obtain this information would require inserting 16 new

rows, each with formulas. So, for this example, creating four calculated items may be

an easier task.

To create a calculated item to compute the commission for January, February, and March, follow

these steps:

1.
Move the cell pointer to the Row Labels area of the pivot table and choose PivotTable

Tools

➜

Options

➜

Calculations

➜

Fields, Items, & Sets

➜

Calculated Item. Excel displays the

Insert Calculated Item dialog box.

2.
Type a name for the new item in the Name box and specify the formula in the Formula

box (see Figure 18-35).

The formula can use items in other fields, but it can’t use worksheet functions. For this

example, the new item is named Qtr1 Commission, and the formula appears as follows:

=10%*(Jan+Feb+Mar)

3.
Click Add.