Creating a Frequency Distribution in Office 365 Excel is easier thanks to dynamic arrays.
First: make sure you have a function called UNIQUE. You don't need UNIQUE in this method, but if you don't have UNIQUE, then you don't have spillable arrays and you need to watch this instead:
Included in this video:
1. Creating a frequency distribution from random data using a bins array and the FREQUENCY spilling function.
2. An odd bug with the Histogram chart. Why you could choose Pareto first and then switch back to Histogram to avoid the bug.
3. Using NORM.INV instead of RANDARRAY or RANDBETWEEN to generate data that has a standard normal distribution.
First: make sure you have a function called UNIQUE. You don't need UNIQUE in this method, but if you don't have UNIQUE, then you don't have spillable arrays and you need to watch this instead:
Included in this video:
1. Creating a frequency distribution from random data using a bins array and the FREQUENCY spilling function.
2. An odd bug with the Histogram chart. Why you could choose Pareto first and then switch back to Histogram to avoid the bug.
3. Using NORM.INV instead of RANDARRAY or RANDBETWEEN to generate data that has a standard normal distribution.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2321.
Frequency Distributions are easier with the new Excel dynamic arrays.
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Before we start today I need you to make sure that you have dynamic arrays in your version of Excel.
The fast way to do this is to type equal UNIQ and good means that we see a tooltip.
If Excel has no idea what you're talking about.
This, like equal foobar see - no tooltip appears then you don't have a dynamic arrays and you need to click that "I" in the top right-hand corner to watch this video. Alright so.
Frequency distribution in Excel used to be really, really hard.
That video I mentioned before is one of my best performing videos because it's really hard to do.
So over here in column A are about 5000 sales.
Random numbers between one and 1000, and I'm going to copy those and paste as values.
So - YOU, in theory, are already starting with a whole set of data over there, and then what we want to do is we want to create our bins.
These are called "bins" Here are the groupings of this data. Alright, so less than 10. 10 to 20.
20 to 30.
And while this looks great in the chart, the most important part is this bins array.
Over here on the left hand side, so less than 10 put in 9.99 or I guess even 9.99999.
You choose how many nines you want (after the decimal) and then for 10 to 20 - you put the next level.
And for the very last bin, the greater than 90 we're going to call that the default.
Don't put any bin there. That's the most important part, having no bin .
For your last entry. Every thing else has a bin.
Alright, so to create the formula = FREQUENCY.
The data array is over here in A2, So I'll come over to A2 and Ctrl+Shift+Down arrow.
Alright, now I'm not copying this formula any where, so I don't have to press F4 right now.
The problem, of course is I can't see what I'm the formula where I'm working, so an awesome shortcut here is Ctrl+Backspace It brings me back into the active cell back in to view.
And then the bins array is this little array right here.
Close Paren, beautiful.
We don't have to press any special keystrokes anymore.
Dynamic arrays is just going to make this work and you see that we get one extra value more than the bins are, so that extra value is the everything greater than 89.99.
Alright, now you want to see how this looks right now there is a brand new chart type that will handle this for us, but unfortunately. There's a bug.
Alright, so if you come here to the statistics charts and hover over histogram, see it's doing the wrong thing.
So the fastest way to create a histogram is first choose Pareto chart.
Which is frustrating 'cause it puts the bins as the most common 1st instead of the right way and then go to chart design, Change Chart Type and then choose Histogram. So that's really weird.
Creating a Pareto Chart first and then changing it to a histogram gets you this type of chart.
Now look, just one tip: if you're not already starting out with your own data.
If you're trying to create some data, so just so you can create a tutorial or show that you know how to create frequency distributions rather than use RANDBETWEEN or RANDARRAY. This formula here NORM.INVERSE.
We send it a random number and then we asked for a mean of 50 and let's say a standard deviation of 13. Because 13 times 3 is 39.
50 plus 39 should get us up to 89. 50 - 39, gives us down to 11.
So what is it...
Ninety Nine point some percent will be between 11 and 89 using that formula, so I'll copy that formula here.
And Paste - Ctrl V.
Alright Haha, there we go.
See that's the kind of nice standard normal that were expecting.
And notice that there are some outliers things above 90 in less than five.
They don't show up here in the chart.
Press the plus sign and add data labels to show that there are in fact some out there at those edges, so.
Great improvement now that we have dynamic arrays.
Frequency used to be really, really hard.
because you had to select the right number of cells and you have to remember Control Shift Enter.
All of that goes away.
If you like this tip please Subscribe and ring that bell.
Feel free to post any questions or comments down in the comments below.
My book on Excel Dynamic Arrays. Just $3 for the ebook.
Click that "I" in the top right hand corner.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Frequency Distributions are easier with the new Excel dynamic arrays.
Hey, welcome back to MrExcel netcast. I'm Bill Jelen.
Before we start today I need you to make sure that you have dynamic arrays in your version of Excel.
The fast way to do this is to type equal UNIQ and good means that we see a tooltip.
If Excel has no idea what you're talking about.
This, like equal foobar see - no tooltip appears then you don't have a dynamic arrays and you need to click that "I" in the top right-hand corner to watch this video. Alright so.
Frequency distribution in Excel used to be really, really hard.
That video I mentioned before is one of my best performing videos because it's really hard to do.
So over here in column A are about 5000 sales.
Random numbers between one and 1000, and I'm going to copy those and paste as values.
So - YOU, in theory, are already starting with a whole set of data over there, and then what we want to do is we want to create our bins.
These are called "bins" Here are the groupings of this data. Alright, so less than 10. 10 to 20.
20 to 30.
And while this looks great in the chart, the most important part is this bins array.
Over here on the left hand side, so less than 10 put in 9.99 or I guess even 9.99999.
You choose how many nines you want (after the decimal) and then for 10 to 20 - you put the next level.
And for the very last bin, the greater than 90 we're going to call that the default.
Don't put any bin there. That's the most important part, having no bin .
For your last entry. Every thing else has a bin.
Alright, so to create the formula = FREQUENCY.
The data array is over here in A2, So I'll come over to A2 and Ctrl+Shift+Down arrow.
Alright, now I'm not copying this formula any where, so I don't have to press F4 right now.
The problem, of course is I can't see what I'm the formula where I'm working, so an awesome shortcut here is Ctrl+Backspace It brings me back into the active cell back in to view.
And then the bins array is this little array right here.
Close Paren, beautiful.
We don't have to press any special keystrokes anymore.
Dynamic arrays is just going to make this work and you see that we get one extra value more than the bins are, so that extra value is the everything greater than 89.99.
Alright, now you want to see how this looks right now there is a brand new chart type that will handle this for us, but unfortunately. There's a bug.
Alright, so if you come here to the statistics charts and hover over histogram, see it's doing the wrong thing.
So the fastest way to create a histogram is first choose Pareto chart.
Which is frustrating 'cause it puts the bins as the most common 1st instead of the right way and then go to chart design, Change Chart Type and then choose Histogram. So that's really weird.
Creating a Pareto Chart first and then changing it to a histogram gets you this type of chart.
Now look, just one tip: if you're not already starting out with your own data.
If you're trying to create some data, so just so you can create a tutorial or show that you know how to create frequency distributions rather than use RANDBETWEEN or RANDARRAY. This formula here NORM.INVERSE.
We send it a random number and then we asked for a mean of 50 and let's say a standard deviation of 13. Because 13 times 3 is 39.
50 plus 39 should get us up to 89. 50 - 39, gives us down to 11.
So what is it...
Ninety Nine point some percent will be between 11 and 89 using that formula, so I'll copy that formula here.
And Paste - Ctrl V.
Alright Haha, there we go.
See that's the kind of nice standard normal that were expecting.
And notice that there are some outliers things above 90 in less than five.
They don't show up here in the chart.
Press the plus sign and add data labels to show that there are in fact some out there at those edges, so.
Great improvement now that we have dynamic arrays.
Frequency used to be really, really hard.
because you had to select the right number of cells and you have to remember Control Shift Enter.
All of that goes away.
If you like this tip please Subscribe and ring that bell.
Feel free to post any questions or comments down in the comments below.
My book on Excel Dynamic Arrays. Just $3 for the ebook.
Click that "I" in the top right hand corner.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
