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
![](http://dataempower.net/wp-content/uploads/2021/08/image-2.png)
And below are the results of our AGGREGATE function using the Table1 which happen to be in cells (B2:B7)
![](http://dataempower.net/wp-content/uploads/2021/08/image-4-1024x385.png)