Articles with the tag:
Close
Changelog
Close
Articles with the tag:
Close

AGGREGATE Function

The AGGREGATE function is one of the math and trigonometry functions. The function is used to return 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 syntax is:

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

where

function_num is a numeric value that specifies which function to use. The possible values are listed in the table below.

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

options is a numeric value that specifies which values should be ignored. The possible values are listed in the table below.

Numeric value
Behavior
0 or omitted Ignore nested SUBTOTAL and AGGREGATE functions
1 Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2 Ignore error values, nested SUBTOTAL and AGGREGATE functions
3 Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error values

ref1(2) is up to 253 numeric values or a reference to the cell range containing the values for which you want the aggregate value.

Note: if you want to use one of the following functions: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, or QUARTILE.EXC, ref1 must be a reference to the cell range and ref2 must be the second argument that is required for these functions (k or quart).

Function
Syntax
LARGE LARGE(array, k)
SMALL SMALL(array, k)
PERCENTILE.INC PERCENTILE.INC(array, k)
QUARTILE.INC QUARTILE.INC(array, quart)
PERCENTILE.EXC PERCENTILE.EXC(array, k)
QUARTILE.EXC QUARTILE.EXC(array, quart)

To apply the AGGREGATE function,

  1. select the cell where you wish to display the result,
  2. click the Insert function Insert function icon icon situated at the top toolbar,
    or right-click within a selected cell and select the Insert Function option from the menu,
    or click the Function icon icon situated at the formula bar,
  3. select the Math and trigonometry function group from the list,
  4. click the AGGREGATE function,
  5. enter the required arguments separating them by comma,
  6. press the Enter button.

The result will be displayed in the selected cell.

The figure below displays the result returned by the AGGREGATE function when the SUM function is applied.

AGGREGATE Function

The figure below displays the result returned by the AGGREGATE function when the LARGE function is applied, ref1 is a reference to the cell range, and k is equal to 2. The function returns the second largest value in cells A1-A4.

AGGREGATE Function

Return to previous page
Try now for free Try and make your decision No need to install anything
to see all the features in action