Page 152 - Excel Data Analysis

08 537547 Ch07.qxd 3/4/03 12:07 PM Page 138

EXCEL DATA ANALYSIS

ADD A CALCULATED ITEM

o insert a new item in a field that was not part of the your arguments the constant values or field items listed in
original source data, you can create a calculated item. the Items list box. You cannot use cell references or range
T A calculated item contains a user-created formula to names within a formula for a calculated item. See Chapter 4
determine the value of the item in a particular field. You for more information on creating formulas. For example,
create a calculated item by adding a new item to a field and you can type the following formula to calculate a
then defining a formula that calculates the individual values Maintenance Fee for computer hardware sales:
for the item.
=Hardware * 0.0125
You create a calculated item on the Insert Calculated Item You can create multiple calculated items for each field. Each
dialog box. When you display the dialog box, Excel shows item that you create displays as a row or column on the
where it will add the new calculated item as part of the PivotTable. As you create new calculated items, Excel adds
dialog box title. For example, if you selected the Sales Type them to the Items list for the corresponding field on the
field or corresponding item, the title of the dialog box is, dialog box. You can use a calculated item as an argument in
Insert Calculated Item in "Sales Type".
a new calculated item. For example, you can determine the
You can use any of the standard Excel formula options to total amount earned for hardware and maintenance fees:
create the formula for the calculated item. This includes = Hardware + Maintenance
using built-in Functions and mathematical operators, such
as *, +, and (). If you use a built-in function, you must make

ADD A CALCULATED ITEM

Sales Type

⁄ Select an item within the ¤ Click PivotTable ➪ ‹ Type a label for the ■ You can add an item to the
field where you want to add Formulas ➪ Calculated Item. calculated item. formula by selecting one in
an item. the Items list and clicking
■ The Insert Calculated Item › Type the formula for Insert Item.
dialog box displays. calculating the item values.
ˇ Click Add.
Note: See Chapter 4 for more on
creating formulas.
138