Microsoft Office Tutorials and References
In Depth Information
A few things happen here. The Total row has moved in atop Class Averages, because that latter row
is no longer part of the table . If this looks messy and/or puzzling, you could delete the Class Averages
data, or insert some rows between Total and Class Averages in order to separate the two, which is what
we’ll do. In addition, the data in the last table field—Averages—has been added, and only that field. But
the function the table uses here isn’t SUM, but one we’ve yet to introduce— SUBTOTAL , and it looks like
this, as it appears in cell I2:
=SUBTOTAL(109,[Average])
Here, SUBTOTAL calculates data for a field—in this case Average, the name of the field on the far
right of the table. The 109 refers to the particular mathematical operation being conducted on the
range—which is the default operation, Sum. But click the down arrow by that cell and you’ll see (Figure
6–38):
Figure 6–39. Calculation options for the Total row
You can click any of the possibilities above, and it will give SUBTOTAL a new code which carries out
that operation instead. These are the SUBTOTAL codes representing each of the above operations, as
they appear in the drop-down-menu sequence:
Average - 101
Count All - 102
Count (only cells containing) Numbers – 103
Max -104
Min – 105
Sum – 109
Standard Deviation – 108
Variance – 110
In fact, the SUBTOTAL codes we see above sport an extra “1” because its codes are really 01, 02, etc.
That first digit is still another code, instructing SUBTOTAL to ignore, and so not compute, any data on
table rows you may have hidden via the Hide Rows command (but more importantly, the data on rows
hidden by the AutoFilter will still be computed).
And if you click any cell in the Total row for any of the other fields, a similar drop-down arrow
appears. Click, and you can select from the same options (in fact, you may want to select None for the
Average field in our case—because Sum here is really just adding the test averages of the individual
students, probably a bit of information you don’t need. Note as well that the More Functions… option
allows you to select any of the others in Excel’s collection as well).

Search JabSto ::

Custom Search