Creating a frequency distribution is one of the hardest things to do in Excel, because the function returns an array of values. Episode #1258 will help you group your data into 'Bins' and create a 'Bins Array' using the Frequency Function - Excel 2010
Transcript of the video:
MrExcel podcast is brought to you by Easy-XL. Excel In Depth Chapter 14: Frequency Distribution Oh, hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
In chapter 14, we’re talking about a lot of statistical functions.
In particular, for the podcast, I chose the FREQUENCY function just because it is so hard to use.
What FREQUENCY is used for, when you want to group things into what scientists call bins.
I’m going to use the more friendly term of buckets.
So what I have here is I have about 4,000 results.
The results range from about 10 on up to 90.
They’re in one decimal place.
So 49.5 kinds of things.
And I want to find out how many of those results were less than 10, how many were from 10 to 19.9 and so on.
And so to make this work, you have to build something called a ‘Bins Array’.
And what I’m going to do is, I’m going to build a Bins Array and say, “Hey, I want to see all the records that were 9.99 or less and then everything from, essentially, 10 up to 19.99".
I’ll need to drag that down here and we’ll go down to 89.9.
Alright, so that’s my Bins Array.
And notice that the Bins Array is always going to have one less item than what you want to return.
Right, now to create the frequency function, here’s what you have to do.
You have to select several cells at once, okay.
So the cells adjacent to your Bins Array plus one extra cell.
We’re going to enter one formula in this entire range.
So ‘=FREQUENCY’.
I’m going to say, “Okay, where’s your data?” My data’s over here from A2 down to A4481, comma, and then where’s the Bins Array?
The Bins Array is to the left of my data.
Let’s include that.
And you notice the Bins Array includes one less cell than the number of cells we have selected.
Alright, now, because this is a formula that returns many results and those results are going to be placed in several cells, we have to hold down Control and Shift and press Enter.
And we now have the results here based on this Bins Array.
So less than 9.99, zero records.
But from 10 to 19.99, basically 10 to 20, you have 65 there.
Okay, so it’s a nice formula.
It keeps updating.
Now, if you just need to create this as a snapshot, I’m going to suggest you don’t use the Frequency Array formula at all.
You can just use a pivot table.
Insert>Pivot Table.
Click OK.
Now on our Pivot Table Field List, we’re going to take the result field and drag it down to Row Labels and then take the result field and drag it to Sum Values.
Now, over there in Sum Values, we’re going to change the value field settings to show a count.
Alright. And so, yeah. Alright, great.
It’s saying that there are one record for 10, one record for 10.2, 3 records for 10.3 and so on.
But what we want to do is we want to choose one of those value fields.
Come up here to Group Field and say that we want to group this from 10 to 89.9 in 10 unit groups.
Click OK.
And there’s our pivot table now showing that there were 65 records from 10 to 20, 171 from 20 to 30, and so on.
Matching the results we got there on the FREQUENCY.
The FREQUENCY function still has a use, definitely, because it can automatically update as the numbers change or as we change the Bins, but if you’re just trying to get one.
Maybe just a quick snapshot of the frequency, you can use the pivot table instead.
Right, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
I’m Bill Jelen.
In chapter 14, we’re talking about a lot of statistical functions.
In particular, for the podcast, I chose the FREQUENCY function just because it is so hard to use.
What FREQUENCY is used for, when you want to group things into what scientists call bins.
I’m going to use the more friendly term of buckets.
So what I have here is I have about 4,000 results.
The results range from about 10 on up to 90.
They’re in one decimal place.
So 49.5 kinds of things.
And I want to find out how many of those results were less than 10, how many were from 10 to 19.9 and so on.
And so to make this work, you have to build something called a ‘Bins Array’.
And what I’m going to do is, I’m going to build a Bins Array and say, “Hey, I want to see all the records that were 9.99 or less and then everything from, essentially, 10 up to 19.99".
I’ll need to drag that down here and we’ll go down to 89.9.
Alright, so that’s my Bins Array.
And notice that the Bins Array is always going to have one less item than what you want to return.
Right, now to create the frequency function, here’s what you have to do.
You have to select several cells at once, okay.
So the cells adjacent to your Bins Array plus one extra cell.
We’re going to enter one formula in this entire range.
So ‘=FREQUENCY’.
I’m going to say, “Okay, where’s your data?” My data’s over here from A2 down to A4481, comma, and then where’s the Bins Array?
The Bins Array is to the left of my data.
Let’s include that.
And you notice the Bins Array includes one less cell than the number of cells we have selected.
Alright, now, because this is a formula that returns many results and those results are going to be placed in several cells, we have to hold down Control and Shift and press Enter.
And we now have the results here based on this Bins Array.
So less than 9.99, zero records.
But from 10 to 19.99, basically 10 to 20, you have 65 there.
Okay, so it’s a nice formula.
It keeps updating.
Now, if you just need to create this as a snapshot, I’m going to suggest you don’t use the Frequency Array formula at all.
You can just use a pivot table.
Insert>Pivot Table.
Click OK.
Now on our Pivot Table Field List, we’re going to take the result field and drag it down to Row Labels and then take the result field and drag it to Sum Values.
Now, over there in Sum Values, we’re going to change the value field settings to show a count.
Alright. And so, yeah. Alright, great.
It’s saying that there are one record for 10, one record for 10.2, 3 records for 10.3 and so on.
But what we want to do is we want to choose one of those value fields.
Come up here to Group Field and say that we want to group this from 10 to 89.9 in 10 unit groups.
Click OK.
And there’s our pivot table now showing that there were 65 records from 10 to 20, 171 from 20 to 30, and so on.
Matching the results we got there on the FREQUENCY.
The FREQUENCY function still has a use, definitely, because it can automatically update as the numbers change or as we change the Bins, but if you’re just trying to get one.
Maybe just a quick snapshot of the frequency, you can use the pivot table instead.
Right, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
