AGGREGATE Function Templates
11 11 2013
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.
AGGREGATE(function_num, options, ref1, [ref2], …)
AGGREGATE(function_num, options, array, [k])
The AGGREGATE function syntax has the following arguments (argument:
Required. A number from 1 to 19 that specifies which function to use
Required. A numerical value that determines which values to ignore in the evaluation range for the function
Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value
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:
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.