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.

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.