Excel 2010 now contains 400 Functions. Amongst those new Functions is a new Function called 'AGGREGATE'. AGGREGATE is similar to SUBTOTAL, but with several new tricks, including the ability to ignore Error Values. "Rev Up to Excel 2010" by Bill Jelen
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Rev up to Excel 2010, Chapter 12; Aggregates.
Ah well! there's now 400 functions in Excel 2010, up from 351 a few versions go, a lot of those new functions are... well!
They're renamed of the Statistical functions, they may a lot of the descriptive statistic functions and probability functions have common names and so a lot of those are just duplicate names for one.
Really cool function that I think everyone will be able use it as a function called Aggregate. All right, now you'll see that have a SUM function.
Here that's adding up this column there's some N/As in the column and so that's causing the total to be N/A.
Here's a function called subtotal that offers 11 different calculations.
AGGREGATE is related to subtotal and that it offers the same 11 calculations, Average Count, Max, Min, Product, Standard Deviation, Sum and Variance, as well as some new ones that I really would have liked to have had in.
Subtotal, Median, Large, Small Percentile.
All right, so some is what we're use here.
Next item, we can Ignore hidden rows and other subtotal functions, as other Aggregate functions.
This is basically what you would get when you use subtotal, but they've added some other things here like Ignore error values.
That's exactly what I wants, So I'm going to choose Ignore error values, comma and then specify my range here.
Close broom and answer and even though I have a couple of N/As down in the data.
This is adding up everything other than the N/As.
I know I know I should go back and figure out why the N/A's are there, put the item in the lookup table, but I just want to see, what I have from those and the aggregate function will now let me do that.
Lots of other things you can do in aggregate but great for if you need to do Subtotal with Median, use aggregate, if you need to can order arrow rows, use Aggregate.
Nice nice, new function in Excel 2010.
Okay I want to thank you for stopping by.
See you next time with another netcast from MrExcel.
Rev up to Excel 2010, Chapter 12; Aggregates.
Ah well! there's now 400 functions in Excel 2010, up from 351 a few versions go, a lot of those new functions are... well!
They're renamed of the Statistical functions, they may a lot of the descriptive statistic functions and probability functions have common names and so a lot of those are just duplicate names for one.
Really cool function that I think everyone will be able use it as a function called Aggregate. All right, now you'll see that have a SUM function.
Here that's adding up this column there's some N/As in the column and so that's causing the total to be N/A.
Here's a function called subtotal that offers 11 different calculations.
AGGREGATE is related to subtotal and that it offers the same 11 calculations, Average Count, Max, Min, Product, Standard Deviation, Sum and Variance, as well as some new ones that I really would have liked to have had in.
Subtotal, Median, Large, Small Percentile.
All right, so some is what we're use here.
Next item, we can Ignore hidden rows and other subtotal functions, as other Aggregate functions.
This is basically what you would get when you use subtotal, but they've added some other things here like Ignore error values.
That's exactly what I wants, So I'm going to choose Ignore error values, comma and then specify my range here.
Close broom and answer and even though I have a couple of N/As down in the data.
This is adding up everything other than the N/As.
I know I know I should go back and figure out why the N/A's are there, put the item in the lookup table, but I just want to see, what I have from those and the aggregate function will now let me do that.
Lots of other things you can do in aggregate but great for if you need to do Subtotal with Median, use aggregate, if you need to can order arrow rows, use Aggregate.
Nice nice, new function in Excel 2010.
Okay I want to thank you for stopping by.
See you next time with another netcast from MrExcel.
