Main content

### Course: Statistics and probability > Unit 4

Lesson 7: More on normal distributions# Normal distribution excel exercise

(Long-26 minutes) Presentation on spreadsheet to show that the normal distribution approximates the binomial distribution for a large number of trials. Created by Sal Khan.

## Want to join the conversation?

- Around25:29, the convergence chart seems to have an error whenever you input the probability of finishing an odd number of steps away from the start point. Could this possibly be why?:

Since you have an even number of steps (10, 20, 30,etc.), each one being exactly one unit in directly opposing directions (so that we could say that 1 step right is equivalent to -1 steps left), you cannot finish with an odd number of units away from the center, given an even number of total steps.

Consider this: total steps = S

left steps = L

right steps = R

Left steps + Right steps = total Steps [L+R=S]

Now, total distance from the start (D) = |L-R|

and since S = L+R, solve for L and you get L = S-R

Substitute this into the distance equation:

D = |S-R-R| or, to simplify it, D = |S-2R|

So basically. the distance from the start is the total number of steps minus 2 times the number of steps right. Since R and S have to be whole numbers, to get all the possible results for D, you take the total number of steps minus all multiples of 2. If S is odd, then subtracting any multiple of 2 will keep it odd. If S is even, then subtracting any multiple of 2 will keep it even. Therefore, you cannot have an od distance from the start with an even number of total steps.(32 votes)**Bug 1**: An odd "Final Position" causes fractional (impossible) moves and a jaggy (wrong) graph.**Fix**: Change the "Total moves" formula from =C8+2*$C$6 to =C8+MROUND($C$6,2).**Explanation**: You can't do an odd number of moves and end up in the same position. The "Right Moves" and "Left Moves" formulae will result in non-integers, which will result in a wrong (often >1) "Binomial Probability". So, only add an even number of moves to the last known good "Total moves". MROUND rounds to the nearest multiple of 2.**Bug 2**: A large "Final Position" causes "Binomial Probability" to display "#NUM", limiting the graph domain.**Fix**: Change the "Binomial Probability" formula from =FACT(C8)/(FACT(C10) * FACT(C8-C10)) * $C$3^C10 * $C$4^C9 to =BINOMDIST(C9,C8,0.5,FALSE).**Explanation**: Excel cannot handle numbers larger than 1.8e+308. The factorial of a 171 is larger than this. So, any "Total moves" larger than 170 will result in "#NUM" for "Binomial Probability", which uses factorial. Built-in functions like BINOMDIST avoid large intermediate numbers and get the same result.(25 votes)

- Around4:35what's Sal referring to when the normal curve caused the financial industry to make bad choices?(12 votes)
- A lot of old(er) risk models were based on the assumption of normally distributed risk. What typically happens during "shocks" - financial crises, supply shocks in oil markets, etc - is this is often not the case, and this is when you hear people refer to "fat-tails" or risk located multple deviations away from the center of the distribution. Behavioral finance is a discipline that tries to address this.(25 votes)

- This lesson seems out of place as part of the Statistics and probability / Modeling data distributions unit. We have not yet learned about binomial distributions, if you follow the lessons as presented in sequence. It looks like we will eventually learn these concepts in later units, possibly under Probability or under Random Variables (not sure).(21 votes)
- Why Variance = B8*B4*B3

I know from http://www.khanacademy.org/math/statistics/v/statistics--sample-variance that var=sum of all samples squared - mean squared(8 votes)- Variance of binomial = np(1-p)

For proof, you can do a google search for variance binomial distribution

or

http://www.proofwiki.org/wiki/Variance_of_Binomial_Distribution(12 votes)

- I'm watching this in the statistics and probability playlist but I think it's in the wrong place. Binomial distribution hasn't even been covered yet.(12 votes)
- Hello, this is the closest video I have found to what I was searching for. I need help making a histogram in Excel. I get confused on what data goes into "Input" and "Bin" and each box. If someone could help me get some clarification that would be great!(5 votes)
- Hi Zach,

INPUT=the raw data, BIN= upper interval limit.

For Example: Raw data for heights of 10 people randomly collected are {4.5,3.4,5.5,5.7,4.2,4.5,4.8,3.2,4.1,5}. This would be the INPUT.

If I want to put the data into the follwoing class intervals

less than or equals 3,

greater than 3 but less than or equals 3.5,

greater than 3.5 but less than or eqauls 4.5

and so on...

Then 3,3.5,4,4.5 etc are the BINS.(12 votes)

- Does anybody have an idea why there is a sudden difference between Binominal Dist. and Normal Dist. at
**20 moves**? I mean, the rest of the graph is a nice curve but why does it suddenly drop from 0.0007 (10 moves) to 0.0001 (20 moves)?(8 votes)- you are right but the reason that happened because of the way they did the math.(3 votes)

- I noticed that you are raising e to the negative 1/2 power. My text doesn't have that so could you please address this and confirm that this is correct?(5 votes)
- Sorry everyone, pass up my question. I didn't pick out the numbers the way the I should have read the intalicized text. MY Bad. Excuse me Sal! Thanks for your communal generous insights. I am learning above the Normal Curve. If I approach the mean, I guess that would be a relative term to what "mean"ing means. Thanks to you mathematics meaning has kept on moving forward. I am very grateful for your mission here!(7 votes)

- Considering a case of flipping coins, finding Expected value E(X), or mean, and Variance Var(X) seems pretty straightforward to me:

p - probabiliy of success, q= 1-p (probability of "fail"), n = 10 trials

E(X)=n * p= 10 * 0.5 = 5

Var(X) = n * p * (1-p) = 10 * 0.5 * 0.5 = 0.25

But how could we find the variance considering rolling the dices?

For example we roll dice 10 times, n = 10. From the previous Khan's videos I could say that expected value (expected sum after 10 rolls):

E(X)=10* 1/6 * (1+2+3+4+5+6) = 35

But how to find Variance here? What is the p and q here? And what is considered as "success" and "failure" in this situation?

Thanks(3 votes)- The variance is the mean square deviation from the expected value. Breaking that down...

(1) Calculate the expected value of a roll: 3.5

(2) Calculate the difference from the mean for each outcome: (1-3.5), (2-3.5), (3-3.5), (4-3.5), (5-3.5), (6-3.5).

(3) Square each of the differences, multiply by the associated probability (1/6), and sum the result.

Thus the variance of a single roll of a die is 2.917. The variance of ten rolls is ten times the variance of a single roll, or 29.17.(5 votes)

- In the .xls file I found that the formula for the normal distribution "0 left" "10 right" was =1/($B$11*SQRT(2*3.14159*EXP((B15-$B$9)^2/$B$10))). Shouldn't it have been =1/($B$11*SQRT(2*3.14159)*EXP((B15-$B$9)^2/$B$10))?(3 votes)
- Hi Rytis,

Formula in Sal's excel file is correct.

The EXP term is raised to -1/2. Any term raised to -1/2 = inverse of squareroot of that term. Hence in the excel file it is combined with other squareroot terms in the denominator.

Regards

Raj(3 votes)

## Video transcript

In this video we're going to
cover what is arguably, the single most important concept
in all the statistics. Well if you go into almost any
scientific field you might even argue that it's the single
most important concept. I've actually told people that
it's kind of sad they don't cover this in the
core curriculum. Everyone should know about is
because it touches on every single aspect of our lives and
that's the normal distribution or the Gaussian distribution
or the bell curve. And just to kind of give you a
preview of what it is, my preview will actually make it
seem pretty strange but as we go through this video hopefully
you'll get a little bit more intuition of what
it's all about. The Gaussian distribution or
the normal distribution, they're two words
for the same thing. It was actually Gauss
who came up with it. I think he was studying
astronomical phenomenon when he did. But it's a probability density
function just like we studies the Poisson distribution. It's just like that. And just to give you the
preview it looks like this. The probability of getting
any x, and it's a class of probability
distribution functions. Just like the binomial
distribution is and the Poisson distribution, it's based on
a bunch of parameters. This is how you would
traditionally see it written in a lot of textbooks and if we
have time, I'd like to rearrange the algebra just to
get a little bit more intuition of how it all works out. Or maybe we could get
some insights on where it all came from. I'm not going to prove it in
this video, that's a little bit beyond our scope. Although, I do want to do it
and there's actually some really neat mathematics
that might show up. If you're a math lead there's
something called Sterling's formula what you might want
to do a Wikipedia search on, which is really fascinating. It approximates factorials
with essentially a continuous function. But I won't go into
that right now. The normal distribution is 1
over -- this is how it's normally written -- the
standard deviation times the square root of 2 pi times
e to the minus 1/2. Well, I like to write it this
way, it easier to remember, times whatever value you're
trying to get minus the mean of our distribution divided by the
standard deviation of our distribution squared. And so if you think about it,
actually this is a good thing to just notice right now. This is how far I'm from the
mean and we're dividing that by the standard deviation of
whatever our distribution is. This is a preview of actually a
normal distribution that I've plotted, the purple line here
is a normal distribution. Initially the whole exercise --
I know I jump around a little bit -- is to show you that the
normal distribution is a good approximation for the binomial
distribution and vice versa. If you take enough trials in
your binomal distribution and we'll touch on that a second. The intuition of this term
right here I think is interesting because we're
saying, how far are we away from the mean, we're dividing
by the standard deviation. So this whole term right here
is how many standard deviations we are away from the mean. This is actually called
the standard z score. One thing I found in statistics
is there's a lot of words a lot of definitions and they all
sound very fancy, the standard z score. But the underlying concept
is pretty straightforward. Let's say I had a probability
distribution and I get some x value that's out here and it's
3 and a half the standard deviations is away from the
mean, then it's standard z score is 3 and a half. Anyway let's focus on the
purpose of this video. So that's what the normal
distribution, I guess the probability density
function for the normal distribution looks like. But how did it get there? By the end of this video you
should at least feel comfortable that this is a good
approximation for the binomial distribution if you're
taking enough trials. And that's what's fascinating
about the normal distribution is that if you have the sum --
and I'll do a whole other video on the central limit theorem --
but if you have the sum of many independent trials approaching
infinity, that the distribution of those, even though the
distribution of each of those trials might have been
non-normal but the distribution of the sum of all those trials
approaches the normal distribution. I'll talk more
about that later. But that's why it's such a good
distribution to kind of assume for a lot of underlying
phenomenon. If you're kind of modeling
weather patterns or drug interactions and you we'll talk
about where it might work well and where it might
not work so well. Like sometimes people might
assume things like a normal distribution in finance and
we've see the financial crisis that's led to a lot of
things blowing up but. Anyway, let's go back to this. This is a spreadsheet
right here. I just made a black background
and you can downloaded it at khanacademy.org/downloads
Actually, if you just do that you'll see all of
the downloads. I haven't put it there yet, I'm
going to do it right after I record the videos
downloads/normal distribution.xls. If you just go up to
khanacademy.org/download/ you'll see all the things
there and you'll see this spreadsheet. I encourage you to play with it
and maybe do other spreadsheets were you experiment with it. So this spreadsheet what we do
is we're doing a game or let's say I'm sitting I'm on a street
and I flip a coin, I flip a completely fair coin. If I get heads, this is heads,
I take a step backwards or let's say a step to the left. And if I get a tails I
take a step to the right. So in general I always have a
-- this is a completely fair coin -- I have a 50 percent
chance of taking a step to the left and I have a 50 percent
chance of taking a step to the right. So your intuition there is if
I told you I took a you a thousand flips of the coin
you're going to keep going left and right. If by chance you get a bunch
of heads, you might end up really kind of moving
over to the left. If you get a bunch of tails you
might move over to the right. And we learned already the odds
of getting a bunch of tales or many more tails than heads is a
lot lower than things kind of being equal or close to equal. Right here what I've done --
let me scroll down a little bit, I don't want to lose the
whole thing -- is I have this little assumption here and I
encourage you to fill that out and change it as you like. This is the number
of steps I take. This is the mean number of left
steps and all I did is I got the probability and we
figured out the mean of the binomial distribution. The mean of the binomial
distribution is essentially the probability of taking a left
step times the total number of trials. So that's equal to 5, that's
where that number comes from. And then the variance -- and
I'm not sure if I went over this and I need prove this to
you if I have and I'll make a whole other video on the
variance of the binomial distribution -- is essentially
equal to the number of trials, 10 times the probability of
taking the left step or kind of a successful trial -- I'm
defining left as a successful trial, that could be right as
well -- times the probability of 1 minus the successful trial
or non successful trial. In this case they're equally
probable and that's where I got the 2.5 from. And that's all on
the spreadsheet. If you actually click on
the cell and look at the actual formula I did that. Although sometimes when you
see it in Excel it's a little bit confusing. And this is just the square
root of that number. The standard deviation
is just the square root of the variance. That's just the
square root of 2.5. And so if you look here
this says, OK what is the probability that
I take 0 steps? So I take a total of 10 steps
-- just to understand this spreadsheet -- what is the
probability that I take 0 left steps? And just to make clear, if I
take 0 left steps that means I must have taken 10 right steps. And I calculate this
probability -- I should have drawn maybe a line here -- I
calculate this using the binomial distribution. And how do I do that? Let me actually switch
colors just to make things interesting. Do they have a purple here? I'll do a blue. So blue for binomial. So what I have here is
how many total steps? There's a total of 10 steps. So 10 factorial, that's kind of
the number of trials I have. Of that I'm choosing
0 to go left. So 0 factorial divided by
10 minus 0 factorial. This is 10 choose 0. I'm choosing 0 left steps of
the total 10 steps I'm taking times the probability of 0 left
steps so, it's the probability of a left step, I'm only taking
0 of them times the probability of a right step, and I'm
taking 10 of those. So that's where this number
came from, this .001. That's what the binomial
distribution tells us. And then this one similarly, is
10 factorial over 1 factorial over 10 minus 1 factorial. That's how I get that one. And once again, if you click
on the actual cell you'll see that explained. We've done this multiple times. This is just a
bionomial calculation. Then right here, after
this line right here, you can almost ignore it. I did that so that I can do a
bunch of different scenarios. For example, if I were to go to
my spreadsheet, and instead of doing 10 I wanted to do 20
steps then everything changes. And that's why down here after
you get to a certain point the whole thing just
kind of repeats. I'll let you think
about why I do that. Maybe I should have made
a cleaner spreadsheet. But it doesn't affect the
scatter plot chart that I did. And so this plot in blue, and
you can't see it because the purple is almost right over. Actually let me make it
smaller so that you can see. Let's say I only took 6 steps. Well it's still hard to see the
difference between the two. Once again the whole point
of this is to see that the normal distribution is
a good approximation. But they're so close that
you can't even see the difference on mine. If you only took four steps,
OK, I think you can see here. Let me get my screen draw on. The blue curve is
right around there. This is the binomial. There's only a few points
here, you the points only go up to here. This is if I take 0 steps left,
1 step left, 2 steps left, 3 steps left, 4 steps left. And then I plot it and then I
say what's the probability using the binomial
distribution? And this is my final
position right? If I take 0 steps to the left
then I take 4 steps to the right so my final position
is at 4, so that's the scenario right here. Let me switch my color back to
yellow, it's easier to see. If I take 4 steps to the left,
I take 0 steps to the right and so my final position is
going to be at minus 4. It's going to be here. If I take an equal amount of
both, that's this scenario, then I'm neutral. I'm just stuck in the
middle right here. I take 2 steps to the right and
then I take 2 steps to the left or vice versa, I take 2 steps
to the left and then I take 2 steps right and I
end up right there. Hopefully that makes
a little sense. My phone is ringing. I'll ignore that because
the normal distribution is so important. Actually, my 9 week old son is
watching so this is the first time I have a live audience. He might pick up something
about the normal distribution. So the blue line right here --
I'll trace it maybe in yellow so you can see it -- is the
plot of the binomial distribution. I connected the lines but you
see the binomial distribution look something more like this. This is the probability
of getting to minus 4. This is the probability
of going to minus 2. This right here is
the probability of ending up nowhere. Then this is the probability of
ending up 2 to the right and this is the probability of
ending up 4 to the right. This is the binomial
distribution, I just plotted these points right here. This is 0.375. This is 0.375. That's the height of that. Now what I wanted to show
you is that the normal distribution approximates
the binomial distribution. So this right here, I wanted
to say what does the normal distribution tell me is the
probability of ending up with exactly 0 left steps? This is a little bit tricky. The binomial distribution
is a discreet probability distribution. You can just look at this chart
or look here and you say, what is the probability of having
exactly 1 left step and 3 right steps which puts me right here? Well you just look at this
chart and you say oh, that puts me right there. I just read that probability,
it's actually .25. And I say oh, I have a 25
percent chance of ending up 2 steps to the right. There's a 25 percent chance. The normal distribution
function is a continuous probability distribution so
it's a continuous curve. It looks like that, it's a
bell curve and it goes off to infinity and starts
approaching 0 on both sides. It looks something like that. This is a continuous
probability distribution. You can't just take a point
here and say, what's the probability that I end
up 2 feet to the right? Because if you just say that
there's the actual the probability of being exactly --
and you should watch with my video on probability density
functions -- but the probability of being exactly 2
feet to the right, exactly, I mean I'm talking to the
atoms, is close to 0. You actually have to specify
a range around this. What I assume in this
within a half a foot in either direction. Right? If we're talking about feet. To figure that out what I did
here is I took the value of the probability density
function there. And I'll show you how
I evaluated that. And then I multiply that by 1. So it gives me this area. And I use that as an
approximation for this area. If you really want to be
particular about it what you would do is you would take the
integral of this curve between this point and this point
as a better approximation. We'll do that in the future. But right now I just want to
give you the intuition that the binomial distribution really
does converge to the normal distribution. So how did I get this
number right here? Well I said, what is the
probability that I think 1 left step -- I kind of used less
steps as success -- of one? And that equaled 1 over
the standard deviation. When I only took 4 steps the
standard deviation was 1. So 1 over 1. Actually let me change this. Let me change it to
a higher number. We'll go back to the
example where I'm at 10. So if this is at 10. Let me go back to
my drawing tool. Let me do this calculation. Actually, even better let
me do this calculation. So what's the probability
that I have 2 left steps? If I have 2 left steps I took a
total of 10 steps so I'm going to have 8 right steps and
that puts me 6 to the right. So that's this
point right here. So what's that probability? How do I figure this out
using the probability density function? How do I figure this height? Well I say the probability of
taking 2 left steps -- that's how I calculate it, if you
actually click on the cell you'll see that -- is equal to
1 over the standard deviation, 1.581 -- and I just directly
reference the cell there -- times the square root of 2 pi. I'm always in awe of the whole
notion of e to the i pi is equal to negative 1
and all of that. But there's another
amazing thing. That all of a sudden as we take
many trials we have this formula that has e and pi in it
and square roots but once again these two numbers just
keep showing up. It tells you something about
the order of the universe with a capital o. But let's see, times e to
the minus 1/2 times x. Well x is what we're trying
to calculate, two successes. To to have exactly 2 left,
so it's 2 minus the mean. So the mean is five, 2 minus
five divided by the standard deviation, divided by 1.581,
all of that squared. That's where this
calculation came from. So I told you in the last one
this right here just tells me this value up here. If I want to know this
exact probability, it's the area of this. And if I just take one
line the are is 0. Remember, in this case you can
only be 2 feet away because we're taking very exact steps. But what the normal
distribution is it's the continuous probability density
function so it can tell us what's the probability of
being 2.183 feet away? Which obviously can only happen
if we're taking infinitely small steps every time. But that's what it's use is. It happens when you start
taking an infinite number of steps. But it can approximate
the discreet. And the way I approximate it is
I say oh, what's probability of being within a foot of that. And so I multiply
this height, which I calculate here, times 1. So let's say this has a base of
1, to calculate this area which I use as an approximation. So you just multiply that times
1 and that's what you get here. And I just want to show you. Even with just 10 trials, the
curves, the normal distribution here is in purple and the
binomial distribution is in blue. So they're almost right
on top of each other. As you can take many more steps
they almost converge right on top of each other and I
encourage you to play with the spreadsheets. Actually, let me show
you that they converge. There's a convergence worksheet
on this spreadsheet as well if you click on the bottom
tab on convergence. This is the same thing but I
just want to show you what happens at any given point. Let me explain this
spreadsheet to you. So this is what's
the probability of moving left, right? So this is just saying, I'm
just fixing a point what's the probability -- and you
can change this -- of my final position being 10. And this essentially tells you
that if I take 10 moves, for my final position to be 10 to the
right, I have to take 10 right moves and 0 left moves. That's a typo right there, it
should be moves not movest. If I take 20 moves to end up 10
moves to the right then I have to make 15 right moves
and 5 left moves. Likewise if I take a total of
80 moves, if I think 80 flips of my coin to make me go left
or right, in order end up 10 to the right, I to take 45 right
moves and 35 left moves in any order and it will end up
with 10 to the right. So what I want to figure out
is, as I start taking a bunch of total moves -- here I max it
out at 170 -- if I start flipping this coin an infinite
number of times, I want to figure out what's the
probability that my final position is 10 to the right. And I want to show you that as
you take more and more moves the normal distribution becomes
a better and better approximation for the
binomial distribution. So right here, this calculates
the binomial probability, just the way we did before and you
can look at the cell to figure it out. I used left moves as a success. So this is 10 choose 0 and
we know what that is. It's 10 factorial over 0
factorial over 10 minus 0 factorial times 0.5 to the
0 times 0.5 to the 10th. That's where that
number comes from. If I go to this one right
here is calculated. Actually let me write
it out because I think it's interesting. I have a total of 60 total
moves, so it's 60 factorial over, I have to have 25 left
moves so 25 factorial. So that's I'm 60 minus 25
factorial times the probability of a left move and have 25 of them, times the probability of a right move
and I have 35 of those. So that's just what the
binomial probability distribution will tell us. And then it figures out the
mean and the variance for each of those circumstances and you
could look at the formula. But the mean is just the
probability of having a left move times the
total number of moves. The variance is probability of
left times probability of right times total number of moves. And then the normal
probability, once again, I just use the normal probability. So I approximate
it the same way. And Excel has a normal
distribution function but I actually typed in the formula
because I wanted you kind of see what was under the covers
for that function that Excel actually has. So I actually say what's the
probability of 25 left moves? No, 45 left moves. So I say the probability of
45 left moves is equal to 1 over the standard deviation. So in this situation the
standard deviation is the square root of 25. So it's five times 2 pi times e
to the minus 1/2 times 45 minus the mean, minus 50 over the
standard deviation, which we figured out was 5, squared. So that tells me the value of
what the normal distribution tells me for this situation
with this standard deviation and this mean and then I
multiply that by 1 -- you don't see that in the formula, I
don't actually write times 1 -- to figure out the area
under the curve. Because remember it's a
continuous distribution function. This right here just gives me
the value but to figure out the probability of being within a
foot of it I have to multiply by 1. I'm approximating really. I really should take the
integral from there to there but this little rectangle is
a pretty good approximation. In this chart I show you that
as the total number of moves gets larger and larger the
difference between what the normal probability distribution
tells us and the binomial probability of distribution
tells us, gets smaller and smaller in terms of the
probability of you ending up to 10 moves to the right. And you can change
this number here. Let me change it
just to show you. You could say what's the
probability of being 15 moves to the right? I think that something is
happening with the floating point error because when you
get to large factorials I think something weird
happens out here. You may just have to
get even further out. For 10 you can see clearly that
it converges and I'll trying to figure out why I was getting
those weird saw tooth patterns. Maybe while I do screen capture
something weird is happening. The whole point of this was to
show you that if you want to figure out the probability of
being 10 moves to the right, as you take more and more flips
of your coin the normal distribution becomes a much
better approximation for the actual binomial distribution. And as you approach infinity
they actually converge to each other. Anyway, that's all
for this video. I'll actually do several
more videos on the normal distributions because it is
such an important concept. See you soon.