Ever Heard of Excel’s AGGREGATE Function?
So, I recently ran a poll on LinkedIn, asking:
“Which Excel function is designed to find the maximum value in a dataset while ignoring errors?”
The options were:
- MAX
- IFERROR & MAX
- AGGREGATE
- LARGE
And guess what? A whopping 93% of respondents got it wrong! 😱 That’s when it hit me—the AGGREGATE function is seriously underrated. Most people don’t realize how much time it can save, especially when you’re dealing with big datasets full of errors or hidden rows. Let’s change that.
Why AGGREGATE Should Be Your New Best Friend in Excel
If you’ve ever worked with messy data—think #DIV/0! errors, hidden rows, or pesky subtotals—you know how frustrating it can be to get accurate results. Functions like SUM or AVERAGE might give you a headache because they don’t handle these issues well. But this is where AGGREGATE steps in and shines.
AGGREGATE was designed to work through these data challenges, allowing you to perform calculations while ignoring things like errors, hidden rows, or even nested calculations. Game-changer, right?
How Does AGGREGATE Work?
It’s super flexible. Here’s the basic format:
=AGGREGATE(function_num, options, array, [k])
- function_num: The type of calculation you want to perform (e.g., 9 for SUM, 1 for AVERAGE, 4 for MAX).
- options: This tells Excel what to ignore (e.g., errors, hidden rows).
- array: The range of cells you want the function to work on.
- k: Used for functions like LARGE or SMALL to indicate which value to return (optional for others).
Functions You Can Use in AGGREGATE
Instead of needing multiple functions, AGGREGATE can handle them all. Here’s what you can do:
- AVERAGE
- COUNT
- COUNTA
- MAX
- MIN
- PRODUCT
- STDEV.S
- STDEV.P
- SUM
- VAR.S
- VAR.P
- MEDIAN
- LARGE
- SMALL
- PERCENTILE.INC
- PERCENTILE.EXC
- QUARTILE.INC
- QUARTILE.EXC
AGGREGATE’s “Ignore” Options
This is where AGGREGATE truly shines—you can control what it ignores! Here are the options:
- Ignore hidden rows (1)
- Ignore errors (2)
- Ignore hidden rows and errors (3)
- Ignore nested SUBTOTAL and AGGREGATE functions (4)
- Ignore hidden rows and nested SUBTOTAL/AGGREGATE (5)
- Ignore errors and nested SUBTOTAL/AGGREGATE (6)
- Ignore hidden rows, errors, and nested SUBTOTAL/AGGREGATE (7)
Real-World Example
Imagine you’re working with a dataset full of errors (DIV/0!, #VALUE!) and you need to find the maximum value, but don’t want those errors ruining your calculation. Here’s how the AGGREGATE function saves the day:
Find the Max Value While Ignoring Errors
=AGGREGATE(4, 6, A1:A10)
4 is the function number for MAX.
6 tells Excel to ignore errors.
A1:A10 is the data range.
Boom! AGGREGATE finds the highest number, skips all those errors, and gives you the clean result you need. Compare that with just using MAX, where one error would break the whole calculation. That’s a headache saved!
So, if you’re looking to level up your Excel game and save yourself from endless manual fixes, it’s time to make AGGREGATE your new best friend. Want to discover more cool Excel tricks like this? Follow me on LinkedIn where I break down complex functions in a simple, fun way—sometimes with help from quirky characters like Squecel 🐿️
