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:
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:
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. |
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:
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) |
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:
|
The functions that require the argument k (or the ref2 argument in the reference form of the function) are:
|
Column B of the following spreadsheet shows four examples of the Excel Aggregate function.
|
Formulas:
|
||||||||||||||||||||||||
|
Results:
|
Note that, in the above example spreadsheet:
For further details and examples of the Excel Aggregate function, see the Microsoft Office website.
If you get an error from the Excel Aggregate Function, this is likely to be the #VALUE! error:
| #VALUE! | - |
Occurs if either:
|