Excel - Microsoft Excel Aggregate Function(excel 2010)

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