Microsoft Office Tutorials and References
In Depth Information
Summarizing worksheets by using hidden and filtered rows
Operation number
(ignores values in
manually hidden
rows)
Operation number
(includes hidden
values)
Function
Description
7
107
STDEV.S
Calculates the standard
deviation of the values in
the range by examining a
sample of the values
8
108
STDEV.P
Calculates the standard
deviation of the values in
the range by using all the
values
9
109
SUM
Returns the result of
adding all numbers in the
range together
5
10
110
VAR.S
Calculates the variance of
the values in the range by
examining a sample of the
values
11
111
VAR.P
Calculates the variance of
the values in the range by
using all of the values
As the previous table shows, the SUBTOTAL function has two sets of operations. The first
set (operations 1 through 11) represents operations that include hidden values in their
summary, and the second set (operations 101 through 111) represents operations that
summarize only values visible in the worksheet. Operations 1 through 11 summarize all
cells in a range, regardless of whether the range contains any manually hidden rows. By
contrast, operations 101 through 111 ignore any values in manually hidden rows. What the
SUBTOTAL function doesn’t do, however, is change its result to reflect rows hidden by using
a filter.
The AGGREGATE function extends the capabilities of the SUBTOTAL function. With it, you
can select from a broader range of functions and use another argument to determine
which, if any, values to ignore in the calculation. AGGREGATE has two possible syntaxes,
depending on the summary operation you select. The first syntax is =AGGREGATE(function_
num, options, ref1...) , which is similar to the syntax of the SUBTOTAL function. The other
possible syntax, =AGGREGATE(function_num, options, array, [k]) , is used to create AGGREGATE
functions that use the LARGE , SMALL , PERCENTILE.INC , QUARTILE.INC , PERCENTILE.EXC ,
and QUARTILE.EXC operations.
 
Search JabSto ::




Custom Search