Description
This is a very useful function that actually combines 19 subfunctions that can be activated by choice. Of course, its possible to use those subfunctions separately without AGGREGATE function, but it has a unique advantage to exclude errors, hidden cells, Subtotals in the calculation.
Syntax
=AGGREGATE(function_num, options, array, [k])
Below is the table of function numbers and their function names
| function_num | function name |
| 1 | Average |
| 2 | Count |
| 3 | CountA |
| 4 | Maximum |
| 5 | Minimum |
| 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.ECS |
| 19 | Quartile.ECS |
Examples
For example we have a sample table of data: Table1
And below are the results of our AGGREGATE function using the Table1 which happen to be in cells (B2:B7)
