Syntax
=AGGREGATE(function_num, options, ref1, [k], ...)Arguments
| Argument | Required | Description |
|---|---|---|
function_num | Required | 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 | Required | 0 to 7: which values to skip (hidden rows, error values, nested subtotals) |
ref1 | Required | The range or array |
k | Optional | For functions 14 to 19: the position, percentile or quartile |
How to use AGGREGATE
AGGREGATE takes 3 required arguments and 1 optional argument, and accepts more values in the same pattern. Type =AGGREGATE( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=AGGREGATE(9, 6, A1:A3)Result Data in the sheet
| A | |
|---|---|
| 1 | 1 |
| 2 | =1/0 |
| 3 | 3 |
=AGGREGATE(14, 6, A1:A4, 2)Result Data in the sheet
| A | |
|---|---|
| 1 | 1 |
| 2 | =1/0 |
| 3 | 3 |
| 4 | 5 |
=AGGREGATE(9, 0, A1:A3)Result Data in the sheet
| A | |
|---|---|
| 1 | 1 |
| 2 | =1/0 |
| 3 | 3 |
Related math functions
- ACOSHReturns the inverse hyperbolic cosine of a number.
- ACOTReturns the arccotangent of a number, in radians from 0 to pi.
- ACOTHReturns the inverse hyperbolic cotangent of a number.
- ADDReturns the sum of two numbers, like the + operator.
- ARABICConverts a Roman numeral written as text to a number.
- ASINReturns the arcsine of a number, in radians.
- ASINHReturns the inverse hyperbolic sine of a number.
- ATANReturns the arctangent of a number, in radians.
- ATAN2Returns the angle, in radians, between the x axis and the line to the point (x_num, y_num).