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

AGGREGATE
function.

Number Function

Description

1

Returns the average of the values in the range.

AVERAGE

2

Counts the cells in the range that contain a number.

COUNT

3

Counts the nonblank cells in the range.

COUNTA

4

Returns the largest (maximum) value in the range.

MAX

5

Returns the smallest (minimum) value in the range.

MIN

6

Returns the result of multiplying all numbers in the range.

PRODUCT

7

Calculates the standard deviation of values in the range by

examining a sample of the values.

STDEV.S

8

Calculates the standard deviation of the values in the range

by using all the values.

STDEV.P

9

Returns the result of adding all numbers in the range

together.

SUM

10

Calculates the variance of values in the range by examining

a sample of the values.

VAR.S

11

Calculates the variance of the values in the range by using

all of the values.

VAR.P

12

Returns the value in the middle of a group of values.

MEDIAN

13

MODE.SNG
L

Returns the most frequently occurring number from a

group of numbers.

14

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.

LARGE

(continued)