 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 first a word from our sponsor yeah actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but but that's okay whatever because our merchandise is is better than their stupid stuff anyways like our accounting rocks product line if you're not crunching cords using excel you're doing it wrong a must-have product because the fact as everyone knows of accounting being one of the highest forms of artistic expression means accountants have a requirement the obligation a duty to share the tools necessary to properly channel the creative muse and the muse she rarely speaks more clearly than through the beautiful symmetry of spreadsheets so get the shirt because the creative muse she could use a new pair of shoes if you would like a commercial free experience consider subscribing to our website at accounting instruction dot com or accounting instruction dot thinkific dot com 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 can 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 cattle dot 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 can 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 pluck out our x's which are going to be on the you know the x axis here and then we used our norm dot 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 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 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 7370 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 and then 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 d's 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 black white and center it all right so now let's actually start to build our graph so I'm going build my normal distribution graph using the percentages over here from the norm.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 inserts 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 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 then 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 right now sometimes we might want to have the z score at the bottom as well so I can see the x in terms of x's as well as x's in terms of the z the z score so that would be kind of neat to have so it's a little tricky to do that sometimes and sometimes we have to add it'd be useful to add another column in order to get that z score a second x down here a second x horizontal so to do that let's first get some other data this is going to be the p of x is greater than or equal to 79 so what I'm going to do now is I'm going to say that I want to pick this number up when it gets to 79 or higher right and and everything else I want to be blank so I could do it this way I could just say okay well this is going to be equal to this up to here and then plot that that would work but I want to do it so I can change it automatically by just changing this number so I could use I can use a logic function to do that which would look like this so I'm going to go up top and say if equals if brackets here's my logical test if this number is greater than is greater than or equal to the this number 79 I'm going to f4 that number because I want it to be absolute f4 dollar sign absolute reference in other words dollar sign before the d in the 13 then comma that's what the comma means in my if reference the value what do I want the value to be then I want the value to be this percent value so do that if not comma what do I want I want you to leave it blank meaning double quotes because there's nothing in the middle that's my text format so it'll just do nothing there close it up and enter and that nothing's there because it's under so I'm going to double click the fill handle and I should see it show up down here I'm also going to add the formatting home tab number percentify and decimalize and there we have it now you could get an approximation by summing it up down here alt enter and let's pull it up to the top it guessed wrong the dancing ants need to go up to the top so it'll be dynamic and then let's percentify this one home tab number percentify we get the 1.78 which is not the same thing we got over here you would think what this would be the more correct calculation because you can't just sum it up because we're talking about the area under the curve but now that we have this we would like to add this to our graph so that we can get that pictorial representation so I'm going to go back to my graph over here and say we want to add another data set so I'm going to click on it go to my chart design up top my data up top I'll pull this over here so I can see it a bit better and then we want to be pulling up this data on this side so I'm going to add a whole another data set I'm going to name it with that dynamic name that we put up top so here it is fancy name being added and then the data set I'm going to remove this click here it's a little finicky on this one so be careful you want to put it up top to pick up the whole thing and then control shift down make sure you don't pick up the total so shift up so there it is and then okay so there we have it and so there you have it it draws that kind of line it's a little bit kind of wonky because we don't have a whole lot of data that we used to populate this but it gives you that line so I'm going to say okay and there we have it so I'm going to say okay and there is our line over here at the 79 so we have the 79 you can imagine pictorially that that orange bit then is representing this this part which should have an area under of 120 right we could see the pictorial representation so now I could say all right let's see if I can add a legend to it clicking on the plus button legend so this blue is the p of x the whole thing and then the orange is the p of x greater than or equal to 79 now remember if this p of x is greater than or equal to 79 we also know that the blue one is going to be kind of you know the inverse everything less than or equal to in essence the 79 right but so we can use this one graph then to answer other questions as well such as what would it be less than or equal to would be the blue side but we might want to hold another graph to to represent other questions as well so the next thing that we might have is for example a type of question where it says p of well before we do that let's actually also add the z score to this so I want to add the z score which I can do now so if I like double click on this for example it should open up the format data and then I'm in this area to the right and we have the primary and secondary and we want to have a secondary axis so I'm going to have a secondary axis because I want to add another x so I can show not only the x's but also a z so I'm going to say okay it's a little off kilt right now but I think it will still be okay so I'm going to delete this bit I don't I don't need this so I'm going to click on that and delete it that brings it back to looking correct at least and then we're going to go up to my data up top and on this second data set I'm going to use a different a different x so I'm going to pick it up by selecting the edit and then I'm going to say the x is going to be named or the z so that's the name that we oh I need just the range the range so that's going to be from here down to here no total that looks good so I'm going to say okay make sure it picks up you can see it picking it up over here so that looks correct I'm going to say okay it doesn't look like anything changed until I hit the plus button and in this x axis I'm going to hit the arrow and then say I want a secondary horizontal and so there it is it picks it up closing up this little green now I'd like to bring this down to the bottom you could leave it there looks fine but I'm going to bring it down to the bottom because I'm used to that so I'm going to double click on it I'm over here in the format and I'm going to go down to the labels and then I'm going to say to the label position bring it low bring it low swing low swing I don't know why that song came into my head so there it is so now we can see that the z we can see this x by the z position as well as the x and oftentimes you'll have questions in terms of x's or in terms of a z score okay so now we might have other questions we might say okay well what if we said p of x is less than less than or equal to and let's do the 79 just to show that here now again you could use kind of the same graph because you could say well if p of x is less than or equal to 79 it would be like the inverse of this which would be kind of the blue but we might want a whole nother graph so let's make this dynamic first I'm going to say let's put the 79 out here so I can change that and then it'll change this label automatically double click in the label go into the end of it equals quotes from the p all the way to the equals to make it a text field and then shift or an and to connect it and then that's 79 I'm going to highlight it and replace it putting my cursor down onto that cell you can't see the 79 but it's in there and then I'm going to tie that to with an and a text field quote to close up the brackets end quote and enter so now I can change that 79 to 80 or something and it changes automatically all right so the calculation then is going to be it's going to be a cumulative you can see it here up to and up to the so it's basically the blue bit right so I can say well this is going to be equal to the norm dot dist and then the x is now going to be the 79 comma and the mean is going to be this and then comma the standard deviation we calculated here comma cumulative yes true or just one and enter let's format it home tab number percentify it add some decimals and we're out at 100 percent that seems awfully high let's bring this down to 79 right and so there it is so and we can also see it this way I can say this equals the same one and this is going to be equal to one minus this number right and and then hold on a sec let's percentify at home tab number group percentify and we get something slightly different because it's right on the line there of the 79 if I double clicked on this one and I changed this to this minus 0.1 or 0.1 right then I get a little bit closer but I'm going to keep it where it is because what I would like to do now is to practice plotting another graph like that so I'm going to go over here I'm going to make another column and I'm going to make it dynamic by picking up this here so now this is going to be p of x is less than or equal to in this case 79 and of course again we can change it by changing this dynamic field but I'll keep it there for now let's make this black and white so I'm going to go font tab black and white and let's then center it and so so now I actually want to see it on a different graph this time so I'm going to actually pick up another graph and let's start at our same starting point I'm going to pick up the p of x and shift up so I don't pick up the 100 insert it's an area graph charts drop down I'm going to pick up an area graph down here that's the one and then I'm going to delete the title of it and let's build this in the same format so I'm going to adjust the x's so I'm going to go to the chart data data select and choose the edit I'm going to replace the x's over here with my axis control shift down shift up to not pick up the total okay there they are that looks good so I'm going to say okay then I'm going to add this column this time so I'm going to put my cursor here I'm going to go chart design data and then I'm going to say select data let's go to then the add so I'm going to add data the series is going to be this name remove this stuff down here and I'm going to click on this little icon put my cursor up top so I can make it dynamic picking up the whole column control shift down and let's hold on a second it went too far you've gone too far what are you doing well hold on I don't have anything in here yet I can't do it so I'm going to select the data and then I'll set enter and then I'll say okay nothing's there yet because I haven't added the data which would be useful so I'm going to say okay what am I going to do to get the data I'm going to say using our logic test again because I want to pick everything that is going to be less than the 79 so this time we want to say less than and including so I'm going to be picking this one up you can see that's where kind of the overlap is so we're going to take that up to there so you would think would be like this amount 99.44 if I was to add it up that way doesn't tie out to these numbers over here because the area under the curve so this is going to be equals if brackets and I'm going to say if this number is less than or equal to this 79 this number then comma what do you want you to do I want you to pick up this number then the related percentage but if not comma that's what the comma means I want you to leave it blank double quotes to leave it blank closing up the brackets and enter so it puts that in there I'm going to percentify it home tab number group percentify adding some decimals double clicking on the fill handle to bring it down it doesn't work because I didn't do some absolute references double clicking on it again everything that's outside of my area of work this number in particular d15 I need to make absolute put in my cursor d15 f4 on the keyboard dollar sign before the d and the 15 enter let's try it again put my cursor on it double click and taking it down so there it is so now you can see it brought it down it gives you this little tweak down here so you can see it's quite small on the upper end because we don't have a whole lot of data but you get the idea I can make this let's make this something different now let's make it 76 so now the orange area represents this I can add the legend plus button and say I want the legend and boom so now we have that there and so so now we have that as a dynamic and the orange is representing this column so we can also add the z score of course too so I could say let's double click on this secondary access and then close this up delete this stuff don't need it and then in my data up top I want to go to the second bit and now delete this or change it to be picking up my z scores and okay and boom and bam and now I need to hit the plus button and axes add the secondary axis and there it is plus to get it off of that again turn that off and then double click on this bit I'm going to bring it to the bottom under the labels and then bring it down to the bottom poor father or if you please and then lower it and boom so we can do that so now we've got that graph now the last one you might say okay well what if I what if I want a between graph so there's a couple different ways we could do it but if I'm going to ask a question what if it's between this or that right I'm going to bring this one down here okay well then we can do another we can do another one if we want to do and say I'm going to make one where it's good where I have let's say a lower and an upper to get between right so now I'm going to say something like p of x and I like to do it this way this is probably not the best way to write it but I say I like to say that x is going to be greater than or equal to let's let's say it's going to be greater than equal to 72 and 72 and less than less than or equal to 79 something like that I'll make this a little bit larger so that might not be the most perfect way to write it but that's what kind of makes sense to me right so it's going to be between 72 and 79 including 72 and 79 so I'm going to say the lower bit is 72 upper bit is going to be 79 is the point if I want this to be my title up top I want to make it dynamic so I can change the lower and upper double clicking on this I'm going to say equals quotes from all the way from here to here I want to be text so quotes and then I'm going to say and an and to tie it together I'm going to select the 72 and pick it up from here so it picks up that cell and then this and I want to be text so I need another and and then I'm going to tie it to the text quote of the and so one and is is code and the other and is text so I'm going to tie it to and actually I want the and all the way over to this end quote as text and then another and to tie it to the 79 which I want to be referenced not as a hard coded number but this upper cell and then I also want to tie that to with another and and then quotes around the last bit of text which is closing up the brackets so that's awfully kind of confusing looking but it works beautifully and if you do it a few times it kind of you kind of get you kind of get the hang of it right so if I was going to do that with a with a calculation here it would look something like this we'd have to say I've got to make I've got to take everything up to the upper limit minus everything up to the to the lower limit and so I'm going to say this equals norm dot dist and the x is going to be on the upper limit 79 comma mean 73.7 comma standard deviation 2.3 comma cumulative yes or one closing it up minus norm dot dist tab x lower x 72 comma mean 73 70 comma standard deviation 2.3 comma cumulative yes or one and close it up and enter let's percentify home tab number percentify adding some decimals and so there we have it now if I wanted to to graph that I'm going to say all right let's say this is going to be equal to my dynamic reference over here and then I'll center that home tab alignment center rapid font group black white okay and then I'm going to say okay so so now with my I instead of trying to do an if function that picks both those up I'm just going to take these two and I'm going to say that that I want you to well let's do it this way one way you can do it is you could you could try to plot the lower you can plot the upper and then you can try to sum them up only if something is showing if it's greater than or equal to zero or something like that with an if function or I could do I could do something like this I can say this is going to be equals if brackets and then there's basically two tests that need to be met so I'm going to embed now an and function so now I've got an and within my if so I can have my two tests so if I'm going to say and then and the logic test is going to be this number the x if that is let's say we're going to say that has to be less than or let's say it has to be greater than or equal to the lower bit the 72 and so I'm going to say a comma and we also want to say that this number over here is going to be less than or equal to the upper limit 79 that closes up our and so we've embedded those two conditions in the if using the embedded and and then what then I'm going to put a comma what are we going to do if that's true well if that's true then you pick up the p of x and then comma if it's not true we just want you to leave it blank which is two quotes and then close it up and enter so nothing's there if I double well now I'm going to double click on it again I need absolute references for these two cells which is on in columns d and e so anything on d and e so this one f4 and then this one f4 making them absolute dollar signs before the letters and numbers enter and then double clicking on the fill handle to pull it down and then you can see it basically adds up that middle bit let's highlight this whole thing home tab number group percentify adding some decimals all right so now let's just do another once again our our chart so I'm going to start the same way control shift down shift up insert charts drop down here we want an area chart going to pull that to the side remove the title going to scroll to the side a bit I'll do this a little bit faster since we've seen this a few times I'm going to go to the data up top go to the x's select the data I want to fix these x's selecting this one I'm going to pick my x's control shift down shift up and all right and okay so boom the x's have been entered then I'm going to pick up my second data set going to the charts data again adding the second data set which is going to be named this my dynamic name removing this bit I'm going to select this item so I want to pick up the whole range even though there's only data in the middle of it and say okay so there it is it pulled it in okay boom that looks good and then I'm going to so now you can see that this is the upper limit so right this is in the middle there even though it looks like it's almost kind of picking up the whole bit because that triangle but that's fine so now I'm going to say all right let's double click on this one giving us our format data series make our secondary axis closing this up I'm going to remove this bit because I don't need it and then I'm going to go into my data again second data set changing the secondary axis to be the z score control shift down and okay it didn't pick it up notice that it's a little finicky sometimes because I could see it didn't do it what did you do excel close that up I'm going to select this it's a little finicky control shift down I'm not going to get upset what did you do excel why did you even do that man why did you even do this like I don't even know you anymore excel okay don't be too hard on excel we're going to say there it is so then so then I can hit the plus button and then hit the arrow secondary horizontal and then okay and then I'm going to pull this down to the bottom double clicking on it labels and bring it down to the bottom lower and so now we've got this between one which is dynamic because if I make the upper limit let's say let's say we make it between 74 or something like that now you can see it basically gives you that between so you might not need all of these graphs to to to represent your data one would probably be fine but you do want to be able to build them so that you can represent your data pictorially because it's it's often quite useful to do so let's just do some formatting on this now I'm going to select this whole thing I'll just make it I'll make it home tab number select the bucket let's make it blue let's make it bordered so there we have that and then I can select all of this stuff let's make it home tab font group blue and oh not white undo undo control z let's border that and we could sum these up if we want to alt equal just to sum up our totals alt equal may as well alt equal oh not that way what are you doing what are you doing excel uh you want to pick the stuff above you know what I wanted don't play don't play dumb don't play dumb with me excel I see what you're doing I see what you're doing think you're so smart but I can tell all right so there we go now we should have a space between our table over here I feel like so I'm going to put my cursor on column f right click and insert and then I'm going to clear the formatting in here just so I have that interim white uh bit so they're not all connected together with my table I'm going to go back to my home tab and make some borders around that and so that will help you to basically answer questions and look at it pictorially whether you're giving x's or whether you're giving z scores and then you can and then you can draw and you can draw various graphs even if you can't draw them by hand let's do a quick review on the spelling did I how was how's the spelling looking it's perfect it can't be spell check must be wrong but there it is it's good good enough