The Excel AGGREGATE Function

Basic Description

The Excel AGGREGATE function returns the aggregate in a list or database of values.

The user is able to specify whether hidden rows, error values and/or any nested Subtotal or Aggregate functions are ignored.

The function has two different formats, which are:

Reference Form of the Aggregate Function

The reference form of the Excel Aggregate function performs a user-specified function on one or more supplied values or arrays of values.

The syntax of the function is:

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

Where the function arguments are as follows:

function_num-

A number, between 1 and 19, that refers to the function you want to perform.

(See the function_num table below for a list of possible values).
options-

A number, between 0 and 7, that defines which values to be ignored in the calculation.

(See the options table below for a list of possible values).
ref1, [ref2], ...-

One or more numeric values (or arrays of numeric values), that you want to perform the function on (up to 253 ref arguments can be supplied).

NOTE:  The 'Large', 'Small', 'Percentile' and 'Quartile' functions require a second argument, k, denoting the position in the array. Therefore, if the selected function is one of these, the ref1 argument provides the array of values to the function, and the ref2 argument provides the value of k - this is the same as using the array form of the Aggregate function.


Array Form of the Aggregate Function

The array form of the Excel Aggregate function performs a user-specified function on a supplied array of values.

The syntax of the function is:

AGGREGATE( function_num, options, array, [k] )

Where the function arguments are as follows:

function_num-

A number, between 1 and 19, that refers to the function you want to perform.

(See the function_num table below for a list of possible values).
options-

A number, between 0 and 7, that defines which values to be ignored in the calculation.

(See the options table below for a list of possible values).
array-An array of values that the specified function is to be performed on.
[k]-An integer that denotes the position in the array for functions that require this additional argument (i.e. must be supplied for the 'Large', 'Small', 'Percentile' and 'Quartile' functions)


Options for the Aggregate 'function_num' and 'options' Arguments

Both forms of the Excel Aggregate Function receive the function_num argument (a number between 1 and 19 denoting the function to be performed), and the option argument (a number between 0 and 7 defining which values are to be ignored during the calculation).

Possible values for these arguments, and their meanings, are listed in the tables below:

function_numfunction
1Average
2Count
3Counta
4Max
5Min
6Product
7Stdev.S
8Stdev.P
9Sum
10Var.S
11Var.P
12Median
13Mode.Sngl
14Large
15Small
16Percentile.Inc
17Quartile.Inc
18Percentile.Exc
19Quartile.Exc
optionsvalues to be ignored
0
(or omitted)
Ignore nested Subtotal & Aggregate functions
1Ignore hidden rows and nested Subtotal & Aggregate functions
2Ignore error values and nested Subtotal & Aggregate functions
3Ignore hidden rows, error values and nested Subtotal & Aggregate functions
4Ignore nothing
5Ignore hidden rows
6Ignore error values
7Ignore hidden rows and error values

The functions that require the argument k (or the ref2 argument in the reference form of the function) are:

functionmeaning of k
LargeReturn the k'th largest value
SmallReturn the k'th smallest value
Percentile.Inc
Percentile.Exc
Return the k'th percentile
Quartile.Inc
Quartile.Exc
Return the k'th quartile

Excel Aggregate Function Examples

Column B of the following spreadsheet shows four examples of the the Excel Aggregate function.

 Formulas:
 ABC
13=AGGREGATE( 1, 4, A1, A2, A3, A4, A5 )- Average of values in cells A1-A5
22.5=AGGREGATE( 9, 4, A1, A2, A3, A4, A5 )- Sum of values in cells A1-A5
31=AGGREGATE( 5, 4, A1:A5 )- Minimum of values in cells A1-A5
46=AGGREGATE( 14, 4, A1:A5, 2 )- Second largest value in cells A1-A5
5-2  
 Results:
 ABC
132.1- Average of values in cells A1-A5
22.510.5- Sum of values in cells A1-A5
31-2- Minimum of values in cells A1-A5
463- Second largest value in cells A1-A5
5-2  

Note that, in the above example spreadsheet:


Further details and examples of the Excel Aggregate function are provided on the Microsoft Office website.


Aggregate Function Error

If you get an error from the Excel Aggregate Function, this is likely to be the #VALUE! error:

Common Error
#VALUE!-

Occurs if either:

  • The function_num argument is any number less than 1 or greater than 19;
  • The options argument is any number less than 0 or greater than 7;
  • The function_num argument is between 14 and 19 (denoting the 'Large', 'Small', 'Percentile' or 'Quartile' functions), and no argument [k] is supplied;
  • Any of the supplied arguments are non-numeric.