Microsoft Office Tutorials and References
In Depth Information
visible in the worksheet. Operations 1-11 summarize all cells in a range, regardless of
whether the range contains any manually hidden rows. By contrast, the operations
101-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 new 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.
The following table summarizes the summary operations available for use in the
Returns the average of the values in the range.
Counts the cells in the range that contain a number.
Counts the nonblank cells in the range.
Returns the largest (maximum) value in the range.
Returns the smallest (minimum) value in the range.
Returns the result of multiplying all numbers in the range.
Calculates the standard deviation of values in the range by
examining a sample of the values.
Calculates the standard deviation of the values in the range
by using all the values.
Returns the result of adding all numbers in the range
Calculates the variance of values in the range by examining
a sample of the values.
Calculates the variance of the values in the range by using
all of the values.
Returns the value in the middle of a group of values.
Returns the most frequently occurring number from a
group of numbers.
Returns the k -th largest value in a data set; k is specified
using the last function argument. If k is left blank, Excel
returns the largest value.