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:
The Reference Format
The Array Format
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:
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:
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:
Excel Aggregate Function Examples
The spreadsheet below shows four examples of the the Excel Aggregate function.
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]
Aggregate Function Errors
If you get an error from the Excel Aggregate Function, this is likely to be one of the following:
Common Errors
No comments:
Post a Comment