If you're seeing this message, it means we're having trouble loading external resources on our website.

If you're behind a web filter, please make sure that the domains *.kastatic.org and *.kasandbox.org are unblocked.

Main content

# Normal distribution excel exercise

## Video transcript

in this video we're going to cover what is arguably the single most important concept in all of statistics and maybe well I mean if you go into almost any scientific field you might even argue it's the single most important concept and I've actually told people that it's you know it's kind of sad that they don't cover this you know in the core curriculum everyone should know about this 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 a preview of what it is and you know my preview will actually make it seem pretty strange but as we go through this video hopefully you'll get a little bit more of an intuition of what if it's all about but the defecation distribution or the normal distribution they're just the two words for the same thing it was actually Gauss who came up with it did I think he was he was studying astronomical phenomenon when he did but it's a probability density function just like you know we studied the Poisson distribution it's just like that and just to give you the preview it looks like this that the probability of getting any X and it's a class of of probability distribution functions just like the binomial distribution is and the Poisson distribution is based on a bunch of parameters but it's equal to and 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 you get a little bit more intuition of how it all works out or maybe we could get some insights on 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 in and you know if you if you're a mathlete there's something called Sterling's formula which you might want to do a Wikipedia search on which is really fascinating it it approximates factorials with essentially a continuous function but I won't go into that right now but the the binomial I'm sorry the normal distribution is one over this is that's normally written the standard deviation times the square root of two pi times e to the minus 1/2 and well just I like to write it this way it's easier to remember times whatever value we're trying to get - the mean of our of our distribution divided by the standard deviation of our distribution squared and so if you think about it actually just is 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 and this is a preview of actually a normal distribution that I've plotted the purple line right here is the normal distribution and just you know the whole exercise here 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 the binomial distribution if you take enough trials and your binomial distribution will touch on that a second but it's the intuition of this term right here I think is interesting because we're saying how far we away from the mean we were dividing by the standard deviation we're saying so this whole term right here is how many standard deviations we are away from the mean and this is actually called the standard z-score one thing I found in in statistics is there's a lot of words and a lot of definitions and they all sound very fancy you know the standard z-score but the underlying the underlying concept is pretty pretty straightforward you know let's say I had a probability distribution and you know I get some x value that's out here and it's three and a half standard deviations away from the mean then it's standard z-scores three and a half but anyway let's let's focus on the purpose of this video so that's what the the normal distribution I guess the probability density function for the normal distribution looks like but how did it get there I guess even more importantly I want by the end of this video you should at least feel comfortable that this is a good approximation for the binomial distribution if the but if you take 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 many independent trials you know 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 of those trials approaches the normal distribution and 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 know we'll talk about where it might work well and where it might not work so well like you know sometimes people might assume things like a normal distribution in finance and we see in the financial crisis that's that's led to a lot of things blowing up but anyway let's let's get back to this and this is a spreadsheet right here I just made a black background and you can download it it let me write it right here at khanacademy.org khanacademy.org slash downloads and 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 video this downloads slash normal distribution normal distribution distribution that's distribution that XLS if you just go up to Khan Academy org slash download slash you'll see all of the things there and you'll see that this the spreadsheet I encourage you to play with it and maybe do other spreadsheets where you experiment with it so this spreadsheet what we do is it's it's we're doing a game where 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 let's say if I 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 right so in general I always have a 15 this is a completely fair coin I have a 50% chance of taking a step to the left and I have a 50% chance of taking a step to the right so your intuition there is you know if I told you I took a you know a thousand flips of the coin or like you know you're just going to be 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 tails or many many many many more tales than heads is a lot lower than you know things kind of being into equal it being equal or you know close to equal and right here what I've done this let me know let me turn into the let me scroll down a little bit oh yes okay I don't want to lose the whole thing alright so what I've done right here is i have this little assumption here and i encourage you to fill that out and change it as you like this is a 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 and 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 I need to prove this to you if I have and I'll make a whole other video on what the on the variance of the binomial distribution but the variance is essentially equal to the number of trials ten times the probability of taking the left step or kind of a successful trial that's what you I'm defining left is a successful trial but could be right as well times the probability of you know one minus a 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 in the spreadsheet if you're actually you know click on the cell and look at the the actual form that I did that although sometimes when you see it in Excel it's a little bit confusing and this is just a square root of that number right the standard deviation is just the square root of the variance so that's just the square root of 2.5 and so when if you look here this says okay what is the probability that I take zero steps so I take a total of 10 steps just to understand the spreadsheet so if I take a total of 10 steps what is the probability that I take 0 left steps well and just to make clear if I if I take 0 left steps that means I must have taken 10 right steps and I calculate this probability and this mean I should have drawn maybe a line here this is the binomial eight this using the binomial distribution and how do I do that well it's I say what is the probability that I take is well I take a total of 10 steps so let me actually switch colors just out of just to make things interesting let's see do they have a purple here blue so the buy blue for binomial so what I have here is how many total steps I'm going to take so there's 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 right 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 step 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 point 0 0 1 that's what the binomial distribution tells us and then this one similarly is 10 factorial over 1 factorial over 10 minus 1 factorial and that's how I get that 1 and once again if you click on the actual cell you'll see that explained so this is we've done this multiple times it's just the binomial calculation and then right here after this line right here you can almost ignore it and I did that so that I can do a bunch of different scenarios so for example if I were to go to my spreadsheet if I were to go to my spreadsheet and if instead of doing 10 steps I were to wanted I wanted to do whoops I should use if I were to do 20 steps then everything changes and that's why down here there's you know after you get to kind of a certain point the whole thing just kind of repeats and yeah I'll let you think about why I do that maybe I should have made a cleaner spreadsheet but it doesn't affect the the scatterplot chart that I did and so this plot in blue and you can't see it because the the purple is almost right over and actually let me make it smaller so that you can see so finally took let Stanley took 6 let's say only took 6 steps well it's still hard to see the difference between the two and then once again I mean the whole point of this is to see that the the normal distribution is a good approximation but they're so close that they you can't even see the difference on line see if you only took 4 steps okay I think you can see here the blue here is definitely let me get my screen drawer on so let me draw this the blue curve is right around there right so this is the binomial there's only a few points here right though the points only go up to here this is if I take zero steps left one step left two steps left three steps left four stuff left and then I plot it and then I say what's the probability using the binomial distribution and then this is my final position right if I take zero steps to the left then I take four steps to the right so my final position is at four so that's this scenario right here let me switch my color back to yellow it's easier to see yellow if I take four steps to the left I take zero steps to the right and so my final position is going to be at minus four right it's going to be here if I take an equal amount of both so that's this scenario then I'm neutral I'm just stuck in the middle right here right I take I take two steps to the right and then I take two steps to the left or vice versa I take two steps to the left and then I take two steps right and I end up right there and so hopefully that makes a little sense of how this only seemed like my phone is ringing let me know I'll ignore that because the normal distribution is so important and actually my my nine week old son is watching so this is the first time I have a live audience but I think you might pick up something about the normal distribution but anyway this is so the blue line right here and I'll trace it maybe in yellow just so you can see it is the plot of the binomial distribution and you know I connected the lines but you're often you see the binomial distribution looks something more like this or you know 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 and then this is the probability see whereas actually now the point is right here this is a probability of ending up two to the right and then this is the probability of ending four to the right right that's this is the binomial distribution I just plotted these points right here right this is 0.375 this point three seven five that's the height of that now what I want to show you is that the normal distribution approximates the binomial distribution so this right here I wanted to say what what does the normal distribution tell me is the probability of ending up of ending up with with exactly zero left steps and then this is a little bit tricky because the normal distribution so the the the binomial distribution is a discrete probability distribution you can just look at this chart or look here and you say what is the probability of ending up to you know having exactly having exactly let's say one left step in three right steps which puts me right here well you just look at this chart and you say oh that puts me right there there's I just read that probability it's actually 0.25 and I say oh I have a 25 percent chance of ending up two steps to the what would example is that oh you have two 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 you know it looks like that it's the bell curve and it goes off to infinity and starts approaching zero on both sides alright it looks something like that but this is a continuous probability distribution you can't just take a point here and you say what's the probability that I end up two feet to the right because if you just say that there's the actual the probability of being exactly and you should watch my video on probability density functions but the probability of being exactly two feet to the right exactly I mean I'm talking to the atom is close to zero so you actually have to specify a range around this and what I assume in this is I assume that within essentially a half a foot in either direction right if we're talking about feet so to figure that out what you do what I did here is I took the value of the probability density function there right and I'll show you how I evaluate that and then I multiply that by one so it gives me this area and I use that as an approximation for this area that the if you really wanted to be particular about it what you would do is you would take the integral of the of this curve between this point and this point as a better approximation we'll go that will do that in the future but right now I just want to give you the into that the binomial distribution really does converge to the normal distribution so how did I what is this number right here well I said what is the probability well let me let me do something like that what is let's say this one right here because I don't want to use the zero so what is the probability that I take one left step I kind of used left steps as as a success so what is the probability of one and that equaled one over the standard deviation when I only took four steps the standard deviation was 1 so 1 over 1 actually let me change this because I think it might be it might be let me change it to a higher number let's see if I make this I don't know let me go back to the example where I'm at 10 all right so if this is at 10 let me go back to my drawing tool okay so this calculation right here let me do this calculation actually even better let me do this calculation right here all right so what's the probability that I have two left steps and if I have two 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 you know 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 two 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.58 1 and i just directly reference the cell there divided times the square root of 2 pi and i'll go on you know I always go out and all the whole notion of e to the I pi is equal to negative 1 and all of that but this is another amazing thing that all of a sudden we have this you know as we take many many 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 and it tells you something about I don't know the order of the universe with a capital o but let's see so times e to the minus 1/2 times X well the X is what we're trying to calculate you know two successes so to be to be to have exactly two left so it's two minus the mean so the mean is 5 to minus 5 divided by the standard deviation divided by one point five eight one all of that squared that's where this calculation came from and so and if you really and so I told you in the last one I mean this just tells you this right here just tells me this value up here and I assume that the probability if I were to know this exact probability it's the area of this and if I just take one line the area is zero so to be exactly two feet away using the proper remember I mean in this case you will you can only be two feet away because we're taking very exact steps but what the normal distribution is it's a continuous probability density function so it can tell us what's the probability of being 2.18 three feet away which obviously is only it can only happen if we're kind of taking infinitely small steps every time but that's where it's use is it happens kind of when you start taking an infinite number of steps but it can approximate the discrete and the way I approximate is I say Oh what's the probability of being within a foot of that and so I multiply this height which I calculate here times B one so you know let's say this has a base of one to calculate this area which I use as an approximation so you just multiply that times one and that's what you get here and I just want to show you I mean even with just ten trials the curves for the you know the normal distribution here is in purple and the binomial distribution is in blue so they're almost right on top of each other I mean they started when it when the number of steps I took was a little bit smaller there and as you take many many many many more steps they almost converge right on top of each other I encourage you to play with this spreadsheet and actually let me show you that they converge so I made another there's a convergence worksheet on the spreadsheet as well if you click on the bottom tab run convergence and I did this is the same thing but I just wanted to show you what happens at any given point so let's say that I wanted to let me explain this part the spreadsheet to you so this is you know what's probability of moving left right this is so this is just saying I'm just fixing a point what's the probability and you could change this of my final position being 10 and then this tell essentially tells you that if if I take 10 moves then for my final position to be 10 to the right I have to take 10 right moves and zero left moves and it's a typo right there should be moves not moved if I take 20 moves to end up 10 moves to the right then I have to make 15 right moves and five left moves and likewise if I take a total of 80 moves if I take 80 flips of my coin to make me go left to right in order to end up 10 to the right after take 45 right moves and 35 left moves in any order and it'll end up with 10 to the right so what I want to figure out is as I start taking you know a bunch of total moves it's like my total moves I'm in here I max it out it 170 but you could kind of say is you know if I started flipping this coin an infinite number of times I want to figure out what's the probability that my final position is ten to the right and what I want to show you is is 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 it before and you could look at the cell to figure it out you know this says 10 you know I want I use left moves as a success so this is 10 choose let me you know 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 10 that's where that number comes from if I go to let's say this one right here 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 effect or 60 minus 25 factorial times the probability of a left move and I have 25 of them times the probability of a right move and I have 35 of those right so that's where each of those so that's just where the binomial 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 I just use the D so I approximate it the same way so for example in this situation right here and Excel has a normal distribution function but actually typed in the formula because I wanted to kind of see that you know what was under under the covers for that function that Excel actually has so that you actually say what's the probability of 25 left moves I don't know let's say you know 45 left move 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 5 divided times 2 pi times e to the minus 1/2 times 45 minus the mean minus 50 over the standard deviation which we figure 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 so you don't see that informant I don't actually write x 1 to actually figure out the area under the curve right because remember it's a continuous distribution function like that 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 it by 1 or I'm approximating really I really should take the integral from there to there but this little rectangles are pretty good approximation and this chart I show you that as as the total number of moves gets larger and larger the difference between what the normal probability distribution tells us and the binomial probability distribution tells us gets smaller and smaller in terms of the probability of you ending up 10 moves to the right and you can change this number here let me let me change it just to show you you know you could change it you could say also probably being 15 moves to the let me know I use the wrong tool you could say what's the probability of being me 15 moves to the right and actually that one doesn't 15 moves to the right it looks like it kind of that's not see the 10 and if I go 12 it converges and then if you go to 13 I think that something's happening with the floating point error because you get two large factorials I think something weird happens out of here but like if you do if you do three something weird is happening five ten yeah you maybe have to just get even further out so this for ten you can see clearly that it converges and I'll try to figure out while I was getting those weird sawtooth patterns if you get eleven now everything is maybe it's maybe while I do a screen capture something weird is happening but the whole point of this was to show you that if you wanted 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 continue I'll actually do several more videos on the normal distribution just because it is such an important concept see you soon