Documentation/Calc Functions/AGGREGATE

Function name:
AGGREGATE

Category:
Mathematical

Summary:
AGGREGATE is a powerful but complex function that calculates a single number by applying a selected aggregate function to a specified set of data. Nineteen different aggregate functions are available.

Unlike many other functions that perform similar calculations, AGGREGATE provides options to omit certain unwanted data types from its processing. Options are available to ignore errors, hidden rows, and nested SUBTOTAL and other AGGREGATE function results.

Syntax:
AGGREGATE(Function; Option; Number1[; Number2[; ... [; Number253]]])

or

AGGREGATE(Function; Option; Array; k)

The first syntax is intended for use with Function values 1 to 13, while the second syntax is intended for use with Function values 14 to 19.

Returns:
Returns a numeric value that is the result of applying the selected aggregate function and ignore options to the specified set of data.

Arguments:
Function is an integer value in the range [1, 19], or a reference to a cell containing that number, which specifies the aggregate function to be used. The following table shows the mapping of these values to aggregate functions.

Option is an integer value in the range [0, 7], or a reference to a cell containing that number, which specifies the ignore option to be used. The following table shows which data types are ignored for each value.

Number1, Number2, ..., Number253 are the numeric arguments to be processed when Function is in the range [1, 13]. These are similar to those of the standalone functions used for aggregation, see for example AVERAGE, COUNT, and MAX. Each argument may take one of the following forms:


 * A number, or an expression that evaluates to a number.
 * A reference to a single cell containing a number.
 * A simple reference to a cell range containing numbers (for example, A1:B9).
 * The name of a named range, comprising cells containing numbers.
 * The name of a database range, comprising cells containing numbers.
 * An inline array of numbers (for example, {1, 2, 3, 4}).
 * The text of the heading label above a column of numbers, enclosed in single quotation marks - only available if option is enabled ( on macOS).

Array contains the numeric arguments to be processed when Function is in the range [14, 19]. This argument is similar to the Data argument of the standalone functions used for aggregation, see LARGE, PERCENTILE.EXC, PERCENTILE.INC, QUARTILE.INC, QUARTILE.EXC, and SMALL. The Array argument may take one of the forms listed above for Number1, Number2, ..., Number253.

k is required when Function is in the range [14, 19] and specifies the second argument required by the standalone aggregation function. These are as follows:


 * LARGE requires a RankC argument (positive integer).
 * SMALL requires a RankC argument (positive integer).
 * PERCENTILE.INC requires an Alpha argument (real number in the range [0, 1]).
 * PERCENTILE.EXC requires an Alpha argument (real number in the range (0, 1)).
 * QUARTILE.INC requires a Type argument (integer in the range [0, 4]).
 * QUARTILE.EXC requires a Type argument (integer in the range [1, 3]).

The following error conditions may be encountered:


 * If either Function or Option is non-numeric, then AGGREGATE reports an invalid argument error (Err:502).
 * If either Function or Option is not an integer, the value is truncated to its floor value.
 * If Function is less than 1 or greater than 19, then AGGREGATE reports an invalid argument error (Err:502).
 * If Option is less than 0 or greater than 7, then AGGREGATE reports an invalid argument error (Err:502).
 * Other errors may be generated depending on the aggregate function selected and the data supplied. For example, a #DIV/0! error is generated if Function is set to 7 (STDEV.S) and only one data value is supplied. Another example occurs when a #VALUE! error is generated if Function is set to 13 (MODE.SNGL) and the supplied data contains no duplicate data points. More information about such error conditions can be found on the wiki pages for the standalone versions of the aggregate functions.

Additional details:
The AGGREGATE function is best used for columns of data, rather than rows of data, because hiding columns does not have any effect on the results of AGGREGATE.

Examples:
The following table shows sample data that is used in the subsequent examples of the AGGREGATE function.

Related LibreOffice functions:
AVERAGE

COUNT

COUNTA

LARGE

MAX

MEDIAN

MIN

MODE.SNGL

PERCENTILE.EXC

PERCENTILE.INC

PRODUCT

QUARTILE.EXC

QUARTILE.INC

SMALL

STDEV.P

STDEV.S

SUM

VAR.P

VAR.S

ODF standard:
None

Equivalent Excel functions:
AGGREGATE