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.
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).
To apply the AGGREGATE function,
- select the cell where you wish to display the result,
- click the Insert function 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 icon situated at the formula bar,
- select the Math and trigonometry function group from the list,
- click the AGGREGATE function,
- enter the required arguments separating them by comma,
- 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](https://helpcenter.onlyoffice.com/OfficeWeb/apps/spreadsheeteditor/main/resources/help/it/images/aggregate.png)
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](https://helpcenter.onlyoffice.com/OfficeWeb/apps/spreadsheeteditor/main/resources/help/it/images/aggregate2.png)
Alla pagina precedente