The aggregate function is a powerful tool that enhances data analysis by offering a range of functions while allowing for exclusions, explains Traci Williams
I have to confess that the subject of this article is something that I have NEVER used up until the last couple of weeks, and if I’m really honest, I didn’t even know it existed!
It was brought to my attention by one of the speakers (Martin Weiss) at an ACE event I attended recently in Munich, and it has immense power (while still being simple to understand).
Let me share with you… the AGGREGATE formula:
This formula was introduced to Excel in 2010 and can perform various calculations, from averages to sums, while allowing the user to ignore specific rows or errors. This article delves deep into the intricacies of the AGGREGATE function, its usage, and its advantages.
What Is the AGGREGATE Function?
The AGGREGATE function is a unique formula in Excel that can perform various calculations, from averages to sums. Its distinctiveness lies in its capability to return a result by ignoring hidden rows, error values, or other specific conditions, providing a more refined outcome.
So, in a nutshell, this formula can perform calculations where our standard SUM or AVERAGE formulas would return errors, despite them being the most popular in Excel.
AGGREGATE Function Syntax
The syntax for the AGGREGATE function is:
=AGGREGATE(function_num, options, ref1, [ref2], …)
‘function_num’
A number (1-19) representing the function you want to use (e.g., 1 for AVERAGE, 9 for SUM, etc.).
‘options’
A number (0-7) that specifies which values or cells you wish to ignore during the calculation.
‘ref1, ref2, …’
The range of cells or numbers you want the function to calculate.
Functions
The AGGREGATE function provides 19 (!) different functions, categorized into three types: AVERAGE, COUNT, and SUM. Each function corresponds to a specific number:
1. AVERAGE
– 1: AVERAGE
– 2: COUNT
– 3: COUNTA
– 4: MAX
– 5: MIN
– 6: PRODUCT
– 7: STDEV.S
– 8: STDEV.P
– 9: SUM
– 10: VAR.S
– 11: VAR.P
2. COUNT
– 12: MEDIAN
– 13: MODE.SNGL
– 14: LARGE
– 15: SMALL
3. SUM
– 16: PERCENTILE.INC
– 17: QUARTILE.INC
– 18: PERCENTILE.EXC
– 19: QUARTILE.EXC
Options
For the ‘options’ parameter, you have:
0. Ignore nested SUBTOTAL and AGGREGATE functions
1. Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2. Ignore error values, nested SUBTOTAL and AGGREGATE functions
3. Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4. Ignore nothing
5. Ignore hidden rows
6. Ignore error values
7. Ignore hidden rows and error values
AGGREGATE in Action
In the data below, there is a list of 10 items and their quantities, but column D also includes some errors.
Cell D4 above contains a SUM formula, but this is unable to complete the calculation due to the errors in the data, and so it returns the dreaded #DIV/0! error.
The AGGREGATE formula would be able to complete the calculation, even with these errors, as follows:
In this formula the 9 represents ‘Sum’ and 6 represents ‘Ignore error values’.
Therefore, this formula will sum the range D6:D15, ignoring any error values.
How ACE is that?!
In this example, notice that row 12 has been hidden:
In the formula in row 2, the 9 represents ‘Sum’ and the 7 represents ‘Ignore hidden rows AND error values’.
Therefore, this formula will sum the range D6:D15, ignoring any error values AND hidden rows, giving even more flexibility.
In this final example a different ‘function_num’ has been used:
In the formula in row 1, the 1 represents ‘Average’ and the 6 represents ‘Ignore error values’.
Therefore, this formula will calculate the average of the range D6:D15, ignoring any error values.
Benefits of the AGGREGATE Function
Versatility
With its 19 functions, AGGREGATE is like a Swiss army knife for data calculations.
Refined results
By ignoring errors or hidden rows, it provides a more accurate result, especially in large datasets.
Reduces complexity
Instead of using multiple functions or formulas to get around errors or hidden rows, AGGREGATE simplifies the process.
Conclusion
The AGGREGATE function in Excel is a powerful tool that enhances data analysis by offering a range of functions while allowing for exclusions. It streamlines complex calculations, making data processing more efficient and accurate. Whether you’re a novice Excel user or a seasoned data analyst, understanding and utilizing the AGGREGATE function can significantly elevate your spreadsheet game.
