Page 150 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

ADD A CALCULATED FIELD

requently your data analysis requires special Fields box. You cannot use cell references or range names
calculations. If you want to include a field that within a formula for a calculated field.
F contains a calculation based upon other fields or You can create multiple calculated fields for each PivotTable
customized values in the PivotTable, you can create a report. Each field that you create displays as a separate Data
calculated field. A calculated field is simply an inserted row field. As you create new calculated fields, Excel adds them
or column containing a formula that you create. For to the Fields list on the dialog box. You can use a calculated
example, you can create a calculated field to determine the field as an argument in a new calculated field. For example,
amount of commission paid for each type of sales. The you can determine the total earnings by subtracting the
formula uses the value of the Sales field and multiplies it by calculated commissions from the sales:
the commission amount, similar to the following:
= Amount – Commission Paid
=-(SALES * .10)
Although the calculated fields look like any other field, and
You can use any of the standard Excel formula options on appear in the PivotTable Field list once you create them,
the Insert Calculated Field dialog box to create the formula you can only place them in the Data area. You cannot move
for the calculated field. This includes built-in Functions and them to the Page, Row, or Column areas. For more on the
mathematical operators, such as *, +, and (). If you use a different areas of a PivotTable, see the sections "PivotTable
built-in function, you must make your arguments the Basics" and "A View of the PivotTable Report Fields."
constant values or any of the PivotTable report fields in the

ADD A CALCULATED FIELD

Amount

⁄ Select a cell within the ¤ Click Insert ➪ Calculated ‹ Type a label for the ■ You can add a field to the
PivotTable report. Field. calculated field. formula by selecting one in
the Fields list and clicking
■ The Insert Calculated Field › Type the formula for Insert Field.
dialog box displays. calculating the field values.
ˇ Click Add.
Note: See Chapter 4 for more on
creating formulas.
136