AGGREGATE

Excel Functions

Return aggregate calculation

Syntax

EXCEL

=AGGREGATE(function_num, options, ref1, [ref2], ...)

Arguments

Parameter	Description
`function_num`	Operation to perform (1-19).
`options`	Values to ignore (0-7).
`ref1`	First argument.
`ref2`	[optional] Second argument (k).

Return Value

Depends on function specified

Details

The AGGREGATE function returns the result of an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc. performed on one or more references. The AGGREGATE function is like an upgraded version of the older SUBTOTAL function, and provides more calculation options, and more control over ignoring specific things. There are two reasons that make the AGGREGATE function especially useful, compared to other functions which perform the same operations: AGGREGATE can run a total of 19 functions, and the function to perform is given as a number, which appears as the first argument in the function, function_num. The second argument, options, controls how AGGREGATE handles errors and values in hidden rows. See the table below for all available options. The AGGREGATE function takes four arguments: fun

Examples

Example 1

The last 6 functions require all four arguments: function_num specifies the operation, options controls various behaviors, ref1 is the array of values

EXCEL

LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)

Example #1

To return the MAX value in the range A1:A10, ignoring both errors and hidden rows, provide 4 for function number and 7 for options:

EXCEL

=AGGREGATE(4,7,A1:A10) // max value

Example #1

To return the MIN value with the same options, change the function number to 5:

EXCEL

=AGGREGATE(5,7,A1:A10) // min value

Example #2

In the example shown above, the formula in D5 is:

EXCEL

=AGGREGATE(4,6,values)

Example #3 - nth largest

The formulas in D8:D10 demonstrate how to return "nth largest" values:

EXCEL

=AGGREGATE(14,6,values,1) // 1st largest
=AGGREGATE(14,6,values,2) // 2nd largest
=AGGREGATE(14,6,values,3) // 3rd largest

Example #4 - array operation

What makes AGGREGATE especially useful for more complex formulas is that it can handle arrays natively when the function number is 14-19. For example,

EXCEL

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

AGGREGATE

Syntax

Arguments

Return Value

Details

Examples

See Also