Excel 2010 adds a new function called AGGREGATE. This function is similar to SUBTOTAL but we several new tricks, including MEDIAN, LARGE, SMALL, and the ability to ignore error values
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Excel In Depth chapter 11 – AGGREGATE!
Well hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, Excel 2010 added a lot of functions!
But, you know, a lot of those were simply international issues, or getting the statistics, probability functions to have common names.
But the one really really, really good new function is called AGGREGATE.
So =AGGREGATE, and what AGGREGATE will do is, it will do any of the 11 calculations that we had in the SUBTOTAL function, as well as some ones that have been missing from the SUBTOTAL function.
So MEDIAN, MODE, LARGE, SMALL, PERCENTILE and QUARTILE are all available there, in the AGGREGATE function.
And SUBTOTAL has the ability to ignore other SUBTOTAL functions, right, that's the default behavior, and also, other rows hidden as the result of hiding rows.
But the AGGREGATE function kind of goes a little bit better, because there's three different classes of things that it can ignore.
Can ignore other subtotal rows like SUBTOTAL would do it, can ignore hidden rows, also like SUBTOTAL would do, so like this.
And this basically is, for example, the 9 version of the current SUBTOTAL, and this one here would be the 109 version of the current SUBTOTAL, but then it does something new.
It says “Hey, I'll also ignore error values!” Alright, so the first argument that you're going to pass, just like SUBTOTAL, is which of the, well now, it's 19 functions, not 9 functions you want.
But then the second argument is which things do we want to ignore.
And you see here, based on this little table, we can ignore some things, not other things, and so on, so you find that the right value from this table ignores what you want to do, and then specify the range.
Now, you'll notice that a few things down here in red, these items, which generally will accept an array, can be used in an array formula, which just opens up some whole interesting possibilities.
I really wish they would have made all of these available as an array formula, but they didn't, so let's just, you know, what we get here(?).
So, here's a range of values: 1, 2, 3, 4, and #N/A, and if I SUM that, of course, the sum of that is going to be #N/A, you know, as we expect, #N/A just causes all future formulas to be #N/A. And even the SUBTOTAL function, saying “Hey, let's”, it's going to become #N/A. But the beautiful thing about AGGREGATE, I sum in AGGREGATE here, because I used 6, you see, the 6 says “Ignore Error Values”, well then it just throws that #N/A out, and I get an actual total.
So there's all sorts of situations where you might need to add numbers up, including or excluding hidden rows, other subtotals, or error cells, or some combination that we’re aggregating.
It’s really… (unclear) Well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Excel In Depth chapter 11 – AGGREGATE!
Well hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, Excel 2010 added a lot of functions!
But, you know, a lot of those were simply international issues, or getting the statistics, probability functions to have common names.
But the one really really, really good new function is called AGGREGATE.
So =AGGREGATE, and what AGGREGATE will do is, it will do any of the 11 calculations that we had in the SUBTOTAL function, as well as some ones that have been missing from the SUBTOTAL function.
So MEDIAN, MODE, LARGE, SMALL, PERCENTILE and QUARTILE are all available there, in the AGGREGATE function.
And SUBTOTAL has the ability to ignore other SUBTOTAL functions, right, that's the default behavior, and also, other rows hidden as the result of hiding rows.
But the AGGREGATE function kind of goes a little bit better, because there's three different classes of things that it can ignore.
Can ignore other subtotal rows like SUBTOTAL would do it, can ignore hidden rows, also like SUBTOTAL would do, so like this.
And this basically is, for example, the 9 version of the current SUBTOTAL, and this one here would be the 109 version of the current SUBTOTAL, but then it does something new.
It says “Hey, I'll also ignore error values!” Alright, so the first argument that you're going to pass, just like SUBTOTAL, is which of the, well now, it's 19 functions, not 9 functions you want.
But then the second argument is which things do we want to ignore.
And you see here, based on this little table, we can ignore some things, not other things, and so on, so you find that the right value from this table ignores what you want to do, and then specify the range.
Now, you'll notice that a few things down here in red, these items, which generally will accept an array, can be used in an array formula, which just opens up some whole interesting possibilities.
I really wish they would have made all of these available as an array formula, but they didn't, so let's just, you know, what we get here(?).
So, here's a range of values: 1, 2, 3, 4, and #N/A, and if I SUM that, of course, the sum of that is going to be #N/A, you know, as we expect, #N/A just causes all future formulas to be #N/A. And even the SUBTOTAL function, saying “Hey, let's”, it's going to become #N/A. But the beautiful thing about AGGREGATE, I sum in AGGREGATE here, because I used 6, you see, the 6 says “Ignore Error Values”, well then it just throws that #N/A out, and I get an actual total.
So there's all sorts of situations where you might need to add numbers up, including or excluding hidden rows, other subtotals, or error cells, or some combination that we’re aggregating.
It’s really… (unclear) Well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
