Math and Trignometric AGGREGATE Function

Description

Returns an aggregate in a list or database. The AGGREGATE Function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

The AGGREGATE Function has two different formats −

Reference Format
Array Format

Reference Format

Syntax

AGGREGATE (function_num, options, ref1, [ref2] …)

Arguments

Argument	Description	Required/Optional
Function_num	A number 1 to 19 that specifies which function to use. Look at the Function_num Table given below to know the Functions.	Required
options	A number, between 0 and 7, that determines which values to be ignored in the calculation of the Function. Look at the options Table given below to know the values.	Required
ref1	The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.	Required
ref2 …	Numeric arguments 2 to 253 for which you want the aggregate value.	Optional

Array Format

Syntax

AGGREGATE (function_num, options, array, [k])

Arguments

Argument	Description	Required/Optional
Function_num	A number 1 to 19 that specifies which function to use. Look at the Function_num Table given below to know the Functions.	Required
options	A number, between 0 and 7, that determines which values to be ignored in the calculation of the Function. Look at the options Table given below to know the values.	Required
array	An array, an array formula, or a reference to a range of cells for which you want the aggregate value.	Required
k	An integer that denotes the position in the array for Functions that require this additional argument. Required for the 'Large', 'Small', 'Percentile' and 'Quartile' Functions Look at the argument k Table given below.	Optional

Function_num Table

Function_num	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV.S
8	STDEV.P
9	SUM
10	VAR.S
11	VAR.P
12	MEDIAN
13	MODE.SNGL
14	LARGE
15	SMALL
16	PERCENTILE.INC
17	QUARTILE.INC
18	PERCENTILE.EXC
19	QUARTILE.EXC

Argument k Table

Function	Meaning of k
Large	Return the k^th largest value
Small	Return the k^th smallest value
Percentile.Inc Percentile.Exc	Return the k^th percentile
Quartile.Inc Quartile.Exc	Return the k^th quartile

Notes

When you enter the AGGREGATE function into a cell on the worksheet, as soon as you type the function_num argument, you will see a list of all Functions that you can use as arguments.
The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. E.g. when you subtotal a horizontal range using option 1, such as AGGREGATE (1, 1, ref1), hiding a column does not affect the aggregate sum value, but, hiding a row in vertical range does affect the aggregate.
If function_num argument is any number less than 1 or greater than 19, AGGREGATE returns a #VALUE! Error.
If options argument is any number less than 0 or greater than 7, AGGREGATE returns a #VALUE! Error.
If argument k is required but not provided, i.e. when the function_num argument is between 14 and 19 (denoting the 'Large', 'Small', 'Percentile' or 'Quartile' functions), AGGREGATE returns a #VALUE! Error.
If any of the specified arguments is non-numeric, AGGREGATE returns a #VALUE! Error.
If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! error value.

Applicability

Excel 2010, Excel 2013, Excel 2016

Example

advanced_excel_math_trigonometric_functions.htm