Page 5 of 8PREVNEXT

PivotTable III: Calculate data in PivotTable reports in Excel 2007

Click Play to view an animation showing how to create a formula by using a calculated field.

Now you'll figure out who gets a bonus, and how much the bonus amount is, by creating a formula using a calculated field. Formulas created using a calculated field can be based on any of the fields the report is created from. When you use a calculated field, a new field is added to the PivotTable report.

Let's say that each salesperson who sells more than $30,000 in any quarter receives a 3 percent bonus in that quarter.

To create the formula, at the top of the window, on the Ribbon, you click the Options tab under PivotTable Tools. In the Tools group, click the arrow on the Formulas button, and then click Calculated Field.

In the Insert Calculated Field dialog box, type a name for the formula in the Name box. In the Formula box, type the formula to find out who gets a bonus: ='Sales Amount' * IF('Sales Amount'>30000,3%). Then click OK.

The formula says that if the sales amount is greater than 30,000 in a quarter, the bonus is 3 percent. The 3 percent is multiplied by Sales Amount. If the sales amount for a quarter is less than 30,000, the bonus amount for that quarter is zero.

A new field, Sum of Bonus $, is inserted in the report, which shows the bonuses that each salesperson will receive.

Click Play to see how to use a calculated field.

There's one thing you may notice. An additional 3 percent bonus is listed in the subtotal rows for the salespeople. Why? Excel runs the calculated field formula row by row. In each subtotal row, where the total is more than 30,000, the formula works in that row the same as for the other rows. The formula is mathematically correct on the subtotal row, but does not give the right answer.

The solution? Turn off the automatic subtotals so that the report shows only the amount for each quarter, with no subtotals for each salesperson. You'll see how to do this in the practice.

Page 5 of 8PREVNEXT