Microsoft Office Tutorials and References

In Depth Information

**Using selected mathematical functions**

The AGGREGATE function

The AGGREGATE function, new in Excel 2013, returns an aggregate of a range or array by

applying one of 19 functions, and it offers the option to ignore hidden rows, error values,

or both. This function is designed for use on columns of data; it is not intended for use with

rows, or ranges of data that are arranged horizontally.

When used with lists and ranges, the AGGREGATE function takes the arguments

(function_num, options, ref1, ref2, …)
, where
function_num
is a number from 1 to 19

indicating the function you want to apply to the selected range, as shown in Table 14-1;
options

is an optional number from 1 to 7 indicating specific instructions for ignoring particular

values, as shown in Table 14-2; and
ref1
,
ref2
, and the like are the cells or ranges you want

to aggregate.

TABLE 14-1
AGGREGATE function numbers

Argument
Description

1

AVERAGE

COUNT

2

COUNTA

3

MAX

4

MIN

5

PRODUCT

6

STDEV

7

STDEV.P

8

SUM

9

VAR.S

10

VAR.P

11

MEDIAN

12

MODE.SNGL

13

LARGE
(ref2 required)

14

SMALL
(ref2 required)

15

PERCENTILE.INC
(ref2 required)

16

QUARTILE.INC
(ref2 required)

17

PERCENTILE.EXC
(ref2 required)

18

QUARTILE.EXC
(ref2 required)

19

A
ref2
argument is required for functions 14 through 19, as indicated in Table 14-1. When

used with arrays, the AGGREGATE function takes the arguments
(function_num, options,