Creating a Frequency Distribution with the FREQUENCY function is fairly difficult. In Episode 605, Ill show you how to quickly produce a frequency distribution using a pivot table.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Let's say that we've done some sort of scientific trial.
We have a thousand results and we want to see how those results are distributed.
Typically people try and do this using the frequency function, which is very difficult to do but let me show you the initial problem if we would just build a chart from this data.
So I'll choose all of the data and use "Insert" "Chart" and say that we want a line chart.
We just get a random mess of data.
The points are all over the place and people say, well wait a second you should sort the chart.
So let me go back and sort the original data set and then we just get a line it really doesn't tell us exactly what the distribution is.
So let me delete this chart and what I'm going to do rather than try and use the frequency function is, I'm going to create a pivot table.
"Data" "Pivot Table and Pivot Chart Report" I'll click "Finish" and I'm going to take the trial result to both the row fields, which are going to give me all of those numbers again and then copy it again to the data items here and change it to be a count.
Instead of a sum I want to see the count. I want to see how many occurred in each one.
Well right, now of course I have the original data set with just one next to every single trial, but here's the cool thing, we're going to right click on the first number , choose "Group and Show Detail" and then "Group" and say that we want to group these up into bins starting at 10.
So maybe I go from -40 up to 150 in groups of 10.
Click "OK" and now I get a beautiful distribution chart showing each result.
When I create a chart out of this Maybe I'll create a column chart.
We have a great distribution of all the different values so rather than try and use the frequency function which is an array formula rather difficult to use, you can create a pivot table and use the group option to group your results up into various bins.
Hey, thanks for something by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Let's say that we've done some sort of scientific trial.
We have a thousand results and we want to see how those results are distributed.
Typically people try and do this using the frequency function, which is very difficult to do but let me show you the initial problem if we would just build a chart from this data.
So I'll choose all of the data and use "Insert" "Chart" and say that we want a line chart.
We just get a random mess of data.
The points are all over the place and people say, well wait a second you should sort the chart.
So let me go back and sort the original data set and then we just get a line it really doesn't tell us exactly what the distribution is.
So let me delete this chart and what I'm going to do rather than try and use the frequency function is, I'm going to create a pivot table.
"Data" "Pivot Table and Pivot Chart Report" I'll click "Finish" and I'm going to take the trial result to both the row fields, which are going to give me all of those numbers again and then copy it again to the data items here and change it to be a count.
Instead of a sum I want to see the count. I want to see how many occurred in each one.
Well right, now of course I have the original data set with just one next to every single trial, but here's the cool thing, we're going to right click on the first number , choose "Group and Show Detail" and then "Group" and say that we want to group these up into bins starting at 10.
So maybe I go from -40 up to 150 in groups of 10.
Click "OK" and now I get a beautiful distribution chart showing each result.
When I create a chart out of this Maybe I'll create a column chart.
We have a great distribution of all the different values so rather than try and use the frequency function which is an array formula rather difficult to use, you can create a pivot table and use the group option to group your results up into various bins.
Hey, thanks for something by. We'll see you next time for another netcast from MrExcel.
