Page 19 of 25PREVNEXT

PivotTable III: Show off your PivotTable skills

Entering a Calculated Field formula

To determine whether the bonus is 12 percent or 7 percent, enter a formula as a calculated field. The Fields list in the dialog box contains the fields from the report, which you can use in your formula.

All the salespeople receive a 7 percent bonus each quarter, but this year there's an additional incentive bonus. Each salesperson who exceeds $50,000 in orders will earn a 12 percent bonus instead of a 7 percent bonus for that quarter.

To figure out the bonus amounts, you need to create your own formula in the data area of the report, which requires using a calculated field. That's the way Excel handles formulas you create yourself inside a report.

To create a calculated field, click PivotTable on the PivotTable toolbar, point to Formulas, and then click Calculated Field. In the Insert Calculated Field dialog box, enter the formula in the Formula box using fields from the Fields list.

Here's the formula we created to find out the bonus percentage each salesperson earned in each quarter:

=IF('Order Amount'>50000,12%,7%)

The formula says that if the order amount is greater than $50,000, the bonus is 12 percent. If not, the bonus is 7 percent.

Tip    Did you notice that the formula contains the field name Order Amount, even though we earlier renamed the field to Order $? The field names listed in the Insert Calculated Field dialog box are the original field names, not the new names we created.

Page 19 of 25PREVNEXT