How can you calculate the MIN or MAX value ignoring one outlier? Or, how can you figure out the MIN or MAX throwing out 5% of the outliers? Episode 760 takes a look at SMALL, LARGE, and PERCENTILE functions.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Boy, all of these questions from the podcast this week and next week are all coming from seminars.
It's the spring seminar season.
I've been out doing all sorts of seminars.
If I have not been able to make it to your town to do a seminar, I want to tell you about a great new product that just came out from Que.
They basically took my entire Power Excel seminar and put it on a DVD-ROM.
It has 6 hours of lessons in 85 tips and they have this great slogan.
They say, we don't want you to watch 6 hours.
We want you to open it up, watch one topic in 2, 3, 4 minutes here, and learn something new, and if you do that every day, you're good to go.
It has video, also has a great little booklet with notes from all of the lessons, and the amazing thing, the amazing thing, is they sell this for $39.99, available at Amazon.
Great little product.
I’m really happy about this, especially…I mean, obviously if you're close to where one of my seminars are coming out, I'd love to meet you and love to have you come out to the seminar, but if you're just looking at the seminars page and you see, he's never getting here, feel free to buy that and you'll get all the tips.
There's a 2003 version and a 2007 version.
Now, a question came in in one of the seminars.
They said, hey, we have the MIN and the MAX function, but, at our company, we want to throw out the smallest and the largest.
We figure that smallest and largest is an outlier and we don't want that to factor into our calculations.
So, of course, the MIN function will take a look at an array and give us the smallest number in that array, and the MAX function will do the same thing but give us the largest number.
[ =MIN(A1:A20) ], [ =MAX(A1:A20) ] If you need to throw out exactly one outlier or exactly two outliers, then there's a great function called =SMALL.
=SMALL.
We say, hey, we want to look through this array and, instead of the smallest value, I want the second smallest value.
So, the ,2 will give us the second smallest value, and then the similar function is LARGE.
Well, LARGE.
I want to look through this array and, instead of the largest value, I want the second largest value.
So, here, sure enough, there is a huge outlier.
The largest number was 1500 but, once you get beyond that, it was 871.
[ =SMALL(A1:A20,2) ], [ =LARGE(A1:A20,2) ] Now, that was the question in the seminar but, for me, I would always worry that there were two outliers and I might want to say, look, I want to take the value that falls at the 10th and 90th percentile.
So, =PERCENTILE, take that array, and for the one at the 10th percentile, I put in 0.1.
That gives us 136, and then percentile that array and ask for 0.9, and it shows me which value falls at each percentile.
[ =PERCENTILE(A1:A20,0.1) ], [ =PERCENTILE(A1:A20,0.9) ] This is just kind of a cool way to throw out a few more outliers just in case you have a situation where, you know, there's a few outliers instead of 1 or 2.
So, if you know that you always want to throw out exactly some number of outliers, you can ask for the second smallest value or the third smallest value or the fourth smallest value.
If you want to throw out everything at the extreme ends, you can use percentile with .1 and .9 to do the same thing.
So, there you have it.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Boy, all of these questions from the podcast this week and next week are all coming from seminars.
It's the spring seminar season.
I've been out doing all sorts of seminars.
If I have not been able to make it to your town to do a seminar, I want to tell you about a great new product that just came out from Que.
They basically took my entire Power Excel seminar and put it on a DVD-ROM.
It has 6 hours of lessons in 85 tips and they have this great slogan.
They say, we don't want you to watch 6 hours.
We want you to open it up, watch one topic in 2, 3, 4 minutes here, and learn something new, and if you do that every day, you're good to go.
It has video, also has a great little booklet with notes from all of the lessons, and the amazing thing, the amazing thing, is they sell this for $39.99, available at Amazon.
Great little product.
I’m really happy about this, especially…I mean, obviously if you're close to where one of my seminars are coming out, I'd love to meet you and love to have you come out to the seminar, but if you're just looking at the seminars page and you see, he's never getting here, feel free to buy that and you'll get all the tips.
There's a 2003 version and a 2007 version.
Now, a question came in in one of the seminars.
They said, hey, we have the MIN and the MAX function, but, at our company, we want to throw out the smallest and the largest.
We figure that smallest and largest is an outlier and we don't want that to factor into our calculations.
So, of course, the MIN function will take a look at an array and give us the smallest number in that array, and the MAX function will do the same thing but give us the largest number.
[ =MIN(A1:A20) ], [ =MAX(A1:A20) ] If you need to throw out exactly one outlier or exactly two outliers, then there's a great function called =SMALL.
=SMALL.
We say, hey, we want to look through this array and, instead of the smallest value, I want the second smallest value.
So, the ,2 will give us the second smallest value, and then the similar function is LARGE.
Well, LARGE.
I want to look through this array and, instead of the largest value, I want the second largest value.
So, here, sure enough, there is a huge outlier.
The largest number was 1500 but, once you get beyond that, it was 871.
[ =SMALL(A1:A20,2) ], [ =LARGE(A1:A20,2) ] Now, that was the question in the seminar but, for me, I would always worry that there were two outliers and I might want to say, look, I want to take the value that falls at the 10th and 90th percentile.
So, =PERCENTILE, take that array, and for the one at the 10th percentile, I put in 0.1.
That gives us 136, and then percentile that array and ask for 0.9, and it shows me which value falls at each percentile.
[ =PERCENTILE(A1:A20,0.1) ], [ =PERCENTILE(A1:A20,0.9) ] This is just kind of a cool way to throw out a few more outliers just in case you have a situation where, you know, there's a few outliers instead of 1 or 2.
So, if you know that you always want to throw out exactly some number of outliers, you can ask for the second smallest value or the third smallest value or the fourth smallest value.
If you want to throw out everything at the extreme ends, you can use percentile with .1 and .9 to do the same thing.
So, there you have it.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
