Excel Function – AGGREGATE()

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_numfunction name
1Average
2Count
3CountA
4Maximum
5Minimum
6Product
7StDEV.S
8StDEV.P
9SUM
10VAR.S
11VAR.P
12Median
13MODE.SNGL
14LARGE
15SMALL
16Percentile.INC
17Quartile.INC
18Percentile.ECS
19Quartile.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)

Leave a Reply

Your email address will not be published. Required fields are marked *