Last January, in Episode #1507, Bill took a look at generating Random Numbers around a Standard Deviation using " =NORM.INV [Normal Inverse]. Today, in Episode #1663 the Question from Gary is: "How do we create a Bell Curve in Microsoft Excel?". Follow along with Bill as he shows us how to use "NORM.S.DIST" to create a Standard Bell Curve or NORM.DIST to create a Normal Distribution around any Mean with a particular Standard Deviation.
Update: to plot your own data on a bell curve, see episode 2217:
Table of Contents
(0:00) Welcome to Create a Bell Curve in Excel
(0:24) Generate 61 numbers from -3 to +3
(0:55) Explanation of three standard deviations
(1:18) Using NORM.S.DIST function
(1:44) Creating the Line chart
(2:05) Mean of zero and standard deviation of 1
(2:10) Changing the mean and standard deviation
(2:49) Using NORM.DIST function
(3:15) Creating a Scatter chart or X-Y chart
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Update: to plot your own data on a bell curve, see episode 2217:
Table of Contents
(0:00) Welcome to Create a Bell Curve in Excel
(0:24) Generate 61 numbers from -3 to +3
(0:55) Explanation of three standard deviations
(1:18) Using NORM.S.DIST function
(1:44) Creating the Line chart
(2:05) Mean of zero and standard deviation of 1
(2:10) Changing the mean and standard deviation
(2:49) Using NORM.DIST function
(3:15) Creating a Scatter chart or X-Y chart
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
This Excel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1663.
Bell Curve.
Hey, everyone.
Welcome back to the MrExcel netcast.
I am Bill Jelen.Today's question has been sent in by Gary.
Gary just wants to generate a bell curve, a standard normal distribution.
And so the way I always do this, I always start at minus three.
I want to go from minus three to plus three and produce a nice little granularity here. So I do =+.1 That'll get me about 60 some points and I will copy that formula down.
On purpose I kind of go too far, right?.
Yep. Good.
And I'm going to delete everything from three on down.
So, right there.
Control shift down arrow and then the delete key.
All right. Why go from minus three to plus three?
Because in a standard normal distribution 99 point something percent of the curve is from three standard deviations below to three standard deviations above and in a standard normal distribution, the mean is zero and standard deviation is one.
Therefore, we are going from minus three to three.
Now to calculate the actual height of the curve, we use =NORM.S The S stands for standard distribution and all it needs is that standard deviation over there.
And cumulative is false.
All right, so we get a really small number there.
Double click to shoot that down and then we will select our data.
Now this could either be a line chart or an x y chart.
I'm here at Excel 2013, so let's see what they recommend.
They are going with a line chart.
Good enough. Click okay.
And there is our standard normal curve.
All right, now this assumes a mean of zero and a standard deviation of one.
What if you have some other type of curve like you know, if we know for example that the mean is 60 and the standard deviation is, I don't know, 10.
Well then what you want to do is you want to be able to go from three standard deviations below the mean to three standard deviations above the mean.
So I want to go from 30 to 90.
Maybe we will just do this in one unit increments this time.
Putting the 30 and the 31.
Go down until we get up to 90. Too far again.
All right, so now we have our numbers to do a standard distribution that is not the normal distribution.
We do use =NORM.
And this time =NORM.DIST Now this time, they need to know the x value.
They need to know the mean, which we said was 60.
They need to know the standard deviation, which we said was 10.
And again, cumulative equals false. No.
Alright, so we'll copy that down.
Put a little heading here of curve and create a chart.
This time I am going to create the scatter chart with the smooth lines.
Make it smaller so we can see the x axis there.
All right, so we now go from 30 to 90 and there is our curve so with any mean or standard deviation that you have, you can create a bell curve using NORM.DIST.
And if you just need to create a simple bell curve, then use NORM.S.DIST Well, I want to thank Gary for sending that question in.
And I want to thank you for stopping by.
I'll see you next time for another netcast.
Learn Excel from MrExcel podcast, episode 1663.
Bell Curve.
Hey, everyone.
Welcome back to the MrExcel netcast.
I am Bill Jelen.Today's question has been sent in by Gary.
Gary just wants to generate a bell curve, a standard normal distribution.
And so the way I always do this, I always start at minus three.
I want to go from minus three to plus three and produce a nice little granularity here. So I do =+.1 That'll get me about 60 some points and I will copy that formula down.
On purpose I kind of go too far, right?.
Yep. Good.
And I'm going to delete everything from three on down.
So, right there.
Control shift down arrow and then the delete key.
All right. Why go from minus three to plus three?
Because in a standard normal distribution 99 point something percent of the curve is from three standard deviations below to three standard deviations above and in a standard normal distribution, the mean is zero and standard deviation is one.
Therefore, we are going from minus three to three.
Now to calculate the actual height of the curve, we use =NORM.S The S stands for standard distribution and all it needs is that standard deviation over there.
And cumulative is false.
All right, so we get a really small number there.
Double click to shoot that down and then we will select our data.
Now this could either be a line chart or an x y chart.
I'm here at Excel 2013, so let's see what they recommend.
They are going with a line chart.
Good enough. Click okay.
And there is our standard normal curve.
All right, now this assumes a mean of zero and a standard deviation of one.
What if you have some other type of curve like you know, if we know for example that the mean is 60 and the standard deviation is, I don't know, 10.
Well then what you want to do is you want to be able to go from three standard deviations below the mean to three standard deviations above the mean.
So I want to go from 30 to 90.
Maybe we will just do this in one unit increments this time.
Putting the 30 and the 31.
Go down until we get up to 90. Too far again.
All right, so now we have our numbers to do a standard distribution that is not the normal distribution.
We do use =NORM.
And this time =NORM.DIST Now this time, they need to know the x value.
They need to know the mean, which we said was 60.
They need to know the standard deviation, which we said was 10.
And again, cumulative equals false. No.
Alright, so we'll copy that down.
Put a little heading here of curve and create a chart.
This time I am going to create the scatter chart with the smooth lines.
Make it smaller so we can see the x axis there.
All right, so we now go from 30 to 90 and there is our curve so with any mean or standard deviation that you have, you can create a bell curve using NORM.DIST.
And if you just need to create a simple bell curve, then use NORM.S.DIST Well, I want to thank Gary for sending that question in.
And I want to thank you for stopping by.
I'll see you next time for another netcast.
