# The Excel AGGREGATE Function

## Function Description

The Excel AGGREGATE function returns the result of a specified operation or function, applied to 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 are 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 (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_num function
1 Average
2 Count
3 Counta
4 Max
5 Min
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.Exc
19 Quartile.Exc
options values to be ignored
0
(or omitted)
Ignore nested Subtotal & Aggregate functions
1 Ignore hidden rows and nested Subtotal & Aggregate functions
2 Ignore error values and nested Subtotal & Aggregate functions
3 Ignore hidden rows, error values and nested Subtotal & Aggregate functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error values

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

function meaning of k
Large Return the k'th largest value
Small Return 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 Excel Aggregate function.

Formulas:
A B C
1 3 =AGGREGATE( 1, 4, A1, A2, A3, A4, A5 ) - Average of values in cells A1-A5
2 2.5 =AGGREGATE( 9, 4, A1, A2, A3, A4, A5 ) - Sum of values in cells A1-A5
3 1 =AGGREGATE( 5, 4, A1:A5 ) - Minimum of values in cells A1-A5
4 6 =AGGREGATE( 14, 4, A1:A5, 2 ) - Second largest value in cells A1-A5
5 -2
Results:
A B C
1 3 2.1 - Average of values in cells A1-A5
2 2.5 10.5 - Sum of values in cells A1-A5
3 1 -2 - Minimum of values in cells A1-A5
4 6 3 - Second largest value in cells A1-A5
5 -2

Note that, in the above example spreadsheet:

• The examples in cells B1 and B2, use the reference form of the Aggregate function;
• The examples in cells B3 and B4, use the array form of the Aggregate function;
• In cell B4, the Aggregate uses the 'Large' function, and so it is necessary to supply the argument [k].

For further details and examples of the Excel Aggregate function, see 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.