Microsoft Office Tutorials and References

In Depth Information

**Tip 78: Using a Pivot Table Instead of Formulas**

4.
Specify At Each Change in Gender, Use Function Average, and Add Subtotal to Score.

The result of adding subtotals is shown in Figure 78-2. Notice that Excel also creates an outline, so

you can hide the details and view just the summary.

The formulas inserted by Excel use the SUBTOTAL function, with 1 as the first argument (1 represents

average). Here are the formulas:

=SUBTOTAL(1,B2:B13)

=SUBTOTAL(1,B15:B28)

=SUBTOTAL(1,B2:B28)

The formula in cell B30 calculates the Grand Average and uses a range that includes the other

two SUBTOTAL formulas in cells B14 and B29. The SUBTOTAL function ignores cells that contain

other SUBTOTAL formulas.

Figure 78-2:
Excel adds subtotals automatically.