Watch the video, and/or follow the tutorial. If you want to download the example file.
The AGGREGATE function is available in Excel 2010 and onwards. The function allows you to perform calculations on a range with 19 different functions: COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P, MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC , QUARTILE.INC, PERCENTILE.EXC and QUARTILE.EXC.
AGGREGATE also allows you choose what to ignore when you perform the calculation. You can ignore nested subtotals (created using the SUBTOTAL or AGGREGATE functions) and/or hidden rows and/or error values.
The AGGREGATE function works with filters in that it will only perform the calculation on visible values – just like the SUBTOTAL function.
If you know anything about the SUBTOTAL function you will recognise a lot of similarity between the two. AGGREGATE however gives you the ability to ignore errors and offers an extra 10 functions.
The example below compares the SUM function being used as the standalone SUM function, then within the SUBTOTAL function and then within the AGGREGATE function.
SUM on its own does not ignore nested subtotals, hidden rows and can’t cope with error values. SUBTOTAL using function 9 (SUM) ignores nested subtotals but returns an error when the range it is calculating on includes an error value. SUBTOTAL using function 109 does the same but does ignore hidden rows (row 9 is hidden).
The AGGREGATE function, again using SUM function 9, and using option 3 (see below) ignores everything the SUBTOTAL 109 ignores but also ignores errors.
K Value
The last 6 functions: VAR.P, MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC , QUARTILE.INC, PERCENTILE.EXC and QUARTILE.EXC require a k value as a fourth argument. In the example below in row 16, the LARGE function is used with a k value of 2. This returns the second largest value in the range. Row 17 uses the SMALL function with a k value of 3 – the third smallest value.
