This function calculates an aggregate value in a list or database.
AGGREGATE(functionnum, options, reference1, reference2, …)
or
AGGREGATE(functionnum, options, array, k)
This function has the following arguments:
| Argument | Description |
|---|---|
| functionnum | Refers to the number code that specifies the function to use (see table below). |
| options | Refers to the numerical value that determines which values must be ignored in the evaluation range for the function (see table below) |
| reference1 | Refers to the argument for which you want the aggregate value. |
| reference2 | [Optional] Refers to the additional arguments for which you want the aggregate value. |
| array | Refers to an array, array formula, or a reference to a range of cells for which you want the aggregate value. |
| k | Refers to the additional arguments for which you want the aggregate value. |
The functionnum argument is the number that represents the built-in function to use, as given in this table.
| Built-In Function | Function Code |
|---|---|
| AVERAGE | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| STDEV.S | 7 |
| STDEV.P | 8 |
| SUM | 9 |
| VAR.S | 10 |
| VAR.P | 11 |
| MEDIAN | 12 |
| MODE.SNGL | 13 |
| LARGE | 14 |
| SMALL | 15 |
| PERCENTILE.INC | 16 |
| QUARTILE.INC | 17 |
| PERCENTILE.EXC | 18 |
| QUARTILE.EXC | 19 |
The options argument has the following options.
| Option | Description |
|---|---|
| 0 or omitted | Ignore nested SUBTOTAL and AGGREGATE functions |
| 1 | Ignore hidden rows and nested SUBTOTAL and AGGREGATE functions |
| 2 | Ignore error values and nested SUBTOTAL and AGGREGATE functions |
| 3 | Ignore hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions |
| 4 | Ignore nothing |
| 5 | Ignore hidden rows |
| 6 | Ignore error values |
| 7 | Ignore hidden rows and error values |
This function is designed for columns of data, or vertical cell range. This function has an option to ignore hidden rows and error values.
If a second reference argument is required but it is not provided by the user, a #VALUE! error is returned. Also, if one or more of the references are 3-D references, a #VALUE! error value is returned.
The reference2 argument is optional; however, the following functions are required in this argument.
Accepts numeric values. Returns numeric data.
AGGREGATE(14,6,B1:B7,3) gives the result 5, where B1:B7 is the cell range containing a numeric list.
This function is available in Spread for Windows Forms 11.0 or later.