 statistics and excel normal distribution heights of baseball players data example part number two get ready taking a deep breath holding it in for 10 seconds looking forward to a smooth soothing excel here we are in excel if you don't have access to this workbook that's okay because we basically built this from a blank worksheet but we started in a prior presentation so if you're starting from a blank worksheet you may want to begin back there if you do have access to this workbook we now have six tabs down below the two green example tabs in essence answer key the two blue practice tabs having pre-formatted cells in them so you could get down to the heart of the practice problem the two white blank tabs are where we started with just our data set and a blank workbook so we can practice formatting the cells within excel as we work through the practice problem this is of course where we will continue at this time let's do a recap of what we did in the past we started out with our blank data over here we have data related to baseball so baseball statistics which you can get in different places if you don't have any data sets you might want to go to Kaggle.com is one resource and there's also a lot of baseball related data that you might be able to pick up to practice with we then sorted this information created a table from it picked up what we wanted from it which are the heights and we pulled the heights over to our blank tab as our starting data points we could just work with this information we picked up the mean the standard deviation the median the mode we noted that the mean is similar to the median as well as the mode which is an indication that our data set is following a bell shaped type of curve so might be able to approximate it with a bell curve so we then plotted the bell curve figuring out the bottom and top point of our x's so we can plot out our x's which are going to be on the you know the x axis here and then we used our norm.dist function and we also then compared the actual data in a frequency distribution and look at the percent of the total compared that to the normal distribution so that we see this chart so now we can see that they're pretty similar in nature which again is an indication that that bell curve will give us some predictive power we looked at the difference which also indicates that there's a fairly small differences that the bell curve will give us some predictive power so now what we want to do is think about how can we create a bell curve with an area type of graph and then think about how we can kind of draw a line so we could see the above part of the bell curve the below part of the bell curve and like in between type of questions so let me show you what I mean here we could ask questions such as what if p of x is greater than greater than or equal to let's say 79 and close that out now in order to plot this I'm going to let's let's actually bring this down here and I'm just going to say above this x is going to be 79 or let's actually I'll just keep it where I was I'll just pull this back up what I want to do is make this dynamic so I can change this 79 and it will change this label and we'll also use this label up top so this is our fancy label here I'm going to double click on it and in the front of it I'm going to say equals I'm going to put quotes around this because this is the text all the way from the p to the equal sign I'm going to end that quote so that means it's just going to show up as just the text then I'm going to connect it to you with an and and then this 79 I want to refer to this cell you can't see it anymore because it's covered up but I'm going to select this 79 put my cursor down here and then up to that cell so now that 79 is in that cell so I'm going to replace it with that d13 which will give me the 79 and make it dynamic and then I'm going to say and to connect it again to the last bit of text quotes which is that bracket quotes and then close it up so now it's dynamic so I can make this 80 and my title turns to 80 right now if I was to if I was to do that now I'm looking for everything that is above 79 so if I was to go here you would think it would be from here to here and you can highlight that it comes out to 1.78 but I can't do it I can't do it that way I can't just sum it up because we need the area under the curve so if I was to do this I would have to say one minus the norm dot dist so I can then say okay uh this is going to be equal to one minus norm dot dist and then I can pick up the x which is going to be this and then comma the mean is the 73 70 and then comma the standard deviation 2.3 comma and it needs to be cumulative therefore true or one and close it up and enter so let's percentify that home tab number group percentify add a couple decimals so it's close to to this but uh not well it's pretty close but not exactly the same right now I would like to see that pictorially in a graph so I'd like to be able to plot that pictorially so let's also add the z here and because the z is also a common type of reference to indicate how close someone is to that middle point so it's a great term and so I'm going to then make this white black white and uh let's center it and so the z is going to be calculated as we're going to say equals brackets we're going to pick up each individual x minus the mean and then close up the brackets and divide it by the standard deviation so it's kind of giving us how far out we are the x is in terms of standard deviation in them from the mean so let's double click and copy that down ah wait a sec I can't do that something is wrong something has gone horribly horribly wrong what happened these two numbers need to be absolute referenced so the ones that are in column D so I'm going to put my cursor in D2 F4 on the keyboard D3 F4 on the keyboard dollar sign before the Ds the twos and the threes enter put my cursor back on it then double click the fill handle and boom so then you could see the middle point is at 73 70 so between here and here 73 and 74 that's where you're at that middle point that's where the z score would be at you know the mean at zero okay so then I'm also going to say this is going to be equal to the uh uh this dynamic reference and now we have this dynamic reference up top and we can plot it as well let's make that uh black white and center it all right so now let's actually start to build our graph so I'm going to build my normal distribution graph using the percentages over here from the norm dot dist I'll put my cursor on the title control shift down make sure you don't pick up the 100 shift up so we just have our data insert on the left we're going to say insert this time an area graph and so I'm going to pick this one drop down area something like that so I'll pick that one up I'll pull that to the right and then I'm going to try to try to make this a little bit more fancy so I'm just going to remove the title so I can make it kind of as big as I can and then the the bottom x is not correct because we want to start it not at one but at 64 so I'm going to go up to my chart design my data and then on my x which is on this side edit it and then make it's a little finicky so make sure you're picking it up here control shift down don't pick up the total shift up and then enter and so there we have it so it looks like it's picking them up correctly over here so I'm going to say okay so there we have it so that we could see that that middle point is where we would expect at like the 74 and then you've got this kind of normal distribution that looks about