Excel Spreadsheets: The Aggregate Formula Explained

The aggregate formula is a powerful tool that enhances data analysis by offering a range of functions while allowing for exclusions.

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 formula, its usage, and its advantages.

What is the Aggregate Formula?

The Aggregate formula 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 Formula Syntax

The syntax for the aggregate function is:

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

Let’s break that down and see what it looks like piece by piece:

• 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, (etc): The range of cells or numbers you want the function to calculate.

Functions

The aggregate formula provides 19 different functions, categorised into three types: AVERAGE, COUNT, and SUM. Each function corresponds to a specific number:

1. AVERAGE

• AVERAGE
• COUNT
• COUNTA
• MAX
• MIN
• PRODUCT
• STDEV.S
• STDEV.P
• SUM
• VAR.S
• VAR.P

2. COUNT

• MEDIAN
• MODE.SNGL
• LARGE
• SMALL

3. SUM

• PERCENTILE.INC
• QUARTILE.INC
• PERCENTILE.EXC
• 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 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 Formula

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 formula 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 utilising the aggregate formula can significantly elevate your spreadsheet game.

Take your spreadsheets to the next level. Uncover many useful (and some more complex) formulas to help you unlock even more of Excel’s powerful potential with my On Demand Excel Formulas Workshop.

This article was written by Traci Williams and originally published by Executive Support Magazine.