Microsoft Office Tutorials and References
In Depth Information
Using a Pivot Table Instead of Formulas
4. In the Subtotal dialog box, specify At Each Change in Gender, Use Function Average, and
Add Subtotal to Score.
The result of adding subtotals is shown in Figure 103-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:
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 103-2: Excel adds subtotals automatically.