In this dueling Excel podcast, Bill and Mike look at ways to do a frequency distribution. Episode 1040 shows you how.
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, I'm Bill Jelen, from "MrExcel.com".
I've got a cool Excel tip for you, today.
Hey, this is my choker, an excellent spot on YouTube.
I have a different way to do that.
Hey! Alright, welcome back. It's another dueling Excel podcast.
I'm Bill Jelen, from MrExcel.
We also have Mike Girvin, from Excel Is Fun.
Question today is, how to create a frequency distribution, from this data?
Now, I'm going to let Mike go first today is going to show some formula ways, we'll come back and we'll talk about a pivot table way to do the same thing.
Mike: Thanks, MrExcel.
Hey! We have our data set here and we want to build a frequency distribution.
Now, we have some values we want to group them and then count to get the frequency.
So, our first group will be 10 up to 15.
So, we'll get a count of one.
Our second group will be 15 up to 20, will get a count of 1, 2, 3.
The frequency would be 3.
Let me use the frequency function.
But, the frequency function you have to tell it what the upper limit is for each category.
So, I'm going to type upper type of 15 and a 20, highlight those and then our little fill handle.
Click and drag you can see because we've established a pattern add 5, it knows to increment those.
Now, I'm going to type frequency and our frequency function requires that we highlight the cells, we have 1, 2, 3, 4, 5 categories of 5 cells and we need to have five upper limits and we're going to type frequency.
It wants the data, comma and the upper limits.
Whoops! The upper limits here.
Now, what does the frequency function do, when it gets to this category here?
They'll say, 15 up to and including 20.
So, the upper limit is included when you use the frequency function.
[ ctrl + shift + enter ] This is an array function. [ ctrl + shift + enter ] Alright! Now, what if we didn't want this 15.
We wanted the 15 down here.
So, 15 up to 99. Well!
I'm going to just type penny down here, .01. Copy, highlight the range, right click [ paste special ], [ Operations ].
So, right here will say subtract and instantly it will subtract a penny.
We have some new upper limits.
The frequency function sees, the new upper limits and boom!
It counts exactly what we want.
Now, this is a report, maybe not as explicit as we want.
Maybe we want our labels, to say 10 up to 99.9.
I'm going to click on this sheet and if we scroll over here.
We have categories 10 up to 14.99.
15 up to 19.99.
So, I've created these categories.
So, there's no confusion about the the definition of each category and which count goes into which category.
Now, frequency function upper limit, it includes upper limit sometimes you do not want that.
Let's look at a different example here if you have 10 up to 15, a label like this and the 15 is not included.
So, 10 up to 15, well the frequency function won't work, if you give it a 15.
It will work with the 14.99.
You'd have to use a formula like this.
The SUMPRODUCT for example, you check that whole range for less than the upper limit.
Check the whole range the second time.
Greater than or equal to the lower limit.
So, you see the lower limit is included with this formula the upper limit is not and it counts everything in between.
Now, that's fine.
Another option, if you have 2007 is used to COUNTIFS with an 'S' So, there we have our criteria range and our criteria.
Notice, we had put the comparative operator in quotes and ampersand (&) the upper limit same with the second Criteria greater than or equal to the lower limit.
If that wasn't enough you could also do to COUNTIF formulas.
Now, these examples, we just saw some PRODUCT COUNTIFS, that's for when the lower limit is included but the upper limit is not.
But, if you got your upper limit And it is included.
Wow! frequency function, nice straight forward.
Alright! I'm going to throw it back to MrExcel.
MrExcel: Mike!
Those formulas are enough to make my head spin.
Let's take a look at the easy way to do this, I'm gonna come here to my data.
I'm going to use insert, hit the table.
Click [ ok ].
Now, I'm going to want to put sales down the left-hand side and then sales again in this some values.
Alright! Now, that's going to put it in as sum of sales.
But, I want to change that, I'll choose the first one go to field settings, and say that I want to show this as Count.
There we go so basically we see every item appears exactly once, it's almost useless.
But, check out what we can do.
I'm going to go to the very first item right-click, choose group and then say that I want to start at ten.
Go up to 35, in 5 unit increments.
Click [ ok ] and BAM!
There we are... We're done our frequency distribution great, great way to create this without creating any formulas.
Hey! On behalf of Mike and myself, I want to thank you for stopping by.
We'll see you next time for another dueling Excel podcast.
I've got a cool Excel tip for you, today.
Hey, this is my choker, an excellent spot on YouTube.
I have a different way to do that.
Hey! Alright, welcome back. It's another dueling Excel podcast.
I'm Bill Jelen, from MrExcel.
We also have Mike Girvin, from Excel Is Fun.
Question today is, how to create a frequency distribution, from this data?
Now, I'm going to let Mike go first today is going to show some formula ways, we'll come back and we'll talk about a pivot table way to do the same thing.
Mike: Thanks, MrExcel.
Hey! We have our data set here and we want to build a frequency distribution.
Now, we have some values we want to group them and then count to get the frequency.
So, our first group will be 10 up to 15.
So, we'll get a count of one.
Our second group will be 15 up to 20, will get a count of 1, 2, 3.
The frequency would be 3.
Let me use the frequency function.
But, the frequency function you have to tell it what the upper limit is for each category.
So, I'm going to type upper type of 15 and a 20, highlight those and then our little fill handle.
Click and drag you can see because we've established a pattern add 5, it knows to increment those.
Now, I'm going to type frequency and our frequency function requires that we highlight the cells, we have 1, 2, 3, 4, 5 categories of 5 cells and we need to have five upper limits and we're going to type frequency.
It wants the data, comma and the upper limits.
Whoops! The upper limits here.
Now, what does the frequency function do, when it gets to this category here?
They'll say, 15 up to and including 20.
So, the upper limit is included when you use the frequency function.
[ ctrl + shift + enter ] This is an array function. [ ctrl + shift + enter ] Alright! Now, what if we didn't want this 15.
We wanted the 15 down here.
So, 15 up to 99. Well!
I'm going to just type penny down here, .01. Copy, highlight the range, right click [ paste special ], [ Operations ].
So, right here will say subtract and instantly it will subtract a penny.
We have some new upper limits.
The frequency function sees, the new upper limits and boom!
It counts exactly what we want.
Now, this is a report, maybe not as explicit as we want.
Maybe we want our labels, to say 10 up to 99.9.
I'm going to click on this sheet and if we scroll over here.
We have categories 10 up to 14.99.
15 up to 19.99.
So, I've created these categories.
So, there's no confusion about the the definition of each category and which count goes into which category.
Now, frequency function upper limit, it includes upper limit sometimes you do not want that.
Let's look at a different example here if you have 10 up to 15, a label like this and the 15 is not included.
So, 10 up to 15, well the frequency function won't work, if you give it a 15.
It will work with the 14.99.
You'd have to use a formula like this.
The SUMPRODUCT for example, you check that whole range for less than the upper limit.
Check the whole range the second time.
Greater than or equal to the lower limit.
So, you see the lower limit is included with this formula the upper limit is not and it counts everything in between.
Now, that's fine.
Another option, if you have 2007 is used to COUNTIFS with an 'S' So, there we have our criteria range and our criteria.
Notice, we had put the comparative operator in quotes and ampersand (&) the upper limit same with the second Criteria greater than or equal to the lower limit.
If that wasn't enough you could also do to COUNTIF formulas.
Now, these examples, we just saw some PRODUCT COUNTIFS, that's for when the lower limit is included but the upper limit is not.
But, if you got your upper limit And it is included.
Wow! frequency function, nice straight forward.
Alright! I'm going to throw it back to MrExcel.
MrExcel: Mike!
Those formulas are enough to make my head spin.
Let's take a look at the easy way to do this, I'm gonna come here to my data.
I'm going to use insert, hit the table.
Click [ ok ].
Now, I'm going to want to put sales down the left-hand side and then sales again in this some values.
Alright! Now, that's going to put it in as sum of sales.
But, I want to change that, I'll choose the first one go to field settings, and say that I want to show this as Count.
There we go so basically we see every item appears exactly once, it's almost useless.
But, check out what we can do.
I'm going to go to the very first item right-click, choose group and then say that I want to start at ten.
Go up to 35, in 5 unit increments.
Click [ ok ] and BAM!
There we are... We're done our frequency distribution great, great way to create this without creating any formulas.
Hey! On behalf of Mike and myself, I want to thank you for stopping by.
We'll see you next time for another dueling Excel podcast.
