AGGREGATE Function Templates

I know I learned this function a while ago but then I forgot about it. The aggregate function was newly created for Excel 2010 and it provides us with some interesting functionality: overcoming shortfalls in the performance of such functions as AVERAGE, MIN, MAX, LARGE, SMALL and a few more ... 19 of them altogether. The trouble is, good as it is, it is not that simple to apply. What I have done then is  to create templates that will help you to use the aggregate function in one or both of the formats in which it might be used.

From the Excel Help File:

Returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

Syntax

Reference form

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

Array form

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

The AGGREGATE function syntax has the following arguments (argument:

Function Number

Required. A number from 1 to 19 that specifies which function to use

Options

Required. A numerical value that determines which values to ignore in the evaluation range for the function

Ref 1

Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value

Ref 2

Optional. Numeric arguments 2 to 253 for which you want the aggregate value

For functions that take an array, ref1 is an array, an array formula, or a reference to a range of cells for which you want the aggregate value. Ref2 is a second argument that is required for certain functions. The following functions require a ref2 argument:

• LARGE(array,k)


• SMALL(array,k)


• PERCENTILE.INC(array,k)


• QUARTILE.INC(array,quart)


• PERCENTILE.EXC(array,k)


• QUARTILE.EXC(array,quart)

The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.

My templates will tell you if you are trying to use the wrong format of aggregate and has then been set up to ensure you don't make any other mistakes when using it. This includes providing combobox guidance for choosing which functions and options you need to use.

The Excel file is available here: aggregate_function ... just click!

All feedback is warmly received as are suggestions for improvement.

Duncan Williamson