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,
Search JabSto ::




Custom Search