Excel 2010 adds a new function called 'AGGREGATE'.
This function is similar to SUBTOTAL, but with several new tricks, including MEDIAN, LARGE, SMALL, and the ability to ignore error values. Episode #1231 explores this new Excel Function.
This function is similar to SUBTOTAL, but with several new tricks, including MEDIAN, LARGE, SMALL, and the ability to ignore error values. Episode #1231 explores this new Excel Function.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL Excel in depth, Chapter 11-Aggregate.
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 for 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 one that has 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 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 sub total rows like subtotal do.
It can ignore hidden rows also like subtotal do like this.
And this basically is the is for example of the nine 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 guides.
Alright, so the first argument that you're going to pass, just like subtotal is which of the all now it's 19 functions.
Not nine 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 on so you find that the right value from this table, that's ignores what you want to do.
And then specify the range.
Now, you'll notice that a few things down here are red.
These items which generally will accept an array, can't 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...
What do we there?
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 set an aggregate here.
Because I use six you can see the six has ignore error values.
Well, then it just throws that N/A.
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 were aggregate really.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
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 for 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 one that has 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 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 sub total rows like subtotal do.
It can ignore hidden rows also like subtotal do like this.
And this basically is the is for example of the nine 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 guides.
Alright, so the first argument that you're going to pass, just like subtotal is which of the all now it's 19 functions.
Not nine 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 on so you find that the right value from this table, that's ignores what you want to do.
And then specify the range.
Now, you'll notice that a few things down here are red.
These items which generally will accept an array, can't 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...
What do we there?
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 set an aggregate here.
Because I use six you can see the six has ignore error values.
Well, then it just throws that N/A.
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 were aggregate really.
Hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
