 statistics and excel bell curve test score 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 trust me I'm an accountant product line yeah it's paramount that you let people know that you're an accountant because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions if you would like a commercial free experience consider subscribing to our website at accounting instruction calm or accounting instruction dot think a fake 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 however we started in a prior presentation so you could go back there and start from the blank worksheet but if you do have access to this workbook there's three tabs down below example practice blank example in essence answer key the practice have having pre-format cells so you could get to the heart of the practice problem the blank tab where we started from a blank worksheet and are continuing on it at this point in time let's give a quick recap of what we did in the past we're thinking about the bell curve or normal distribution using an example quite common to both instructors as well as students that being the test scores we first generated our test scores using a random generator tool which is a great tool to understand under the data tab up top and the analysis group if you don't have the analysis group we showed you how to turn that on once we got our data we are imagining these are a thousand data points that we got in actual practice and our test scores and then we did our calculations on that actual data picking up the mean the standard deviation the median noting that the median was close to the mean so that could be an indication that a bell curve would be an approximate representation of this data in order to plot this information in an actual smooth bell curve we then said we want to go for standard deviations lower and higher to pick up basically all of the data even though this the bell curve in theory goes on forever and so for standard deviations here's the lower limit and the upper limit this x representing our test scores this giving our every available or possible test scores this being the probability of those test scores which is our norm dot dist our major function that we've been using we then compared that to our actual data using the frequency distribution to see how many times our actual data fell in the range of say thirty four to thirty five or thirty five to thirty six and then we did a percent of the total of the actual data because this then approximates the the bell curve so now we can compare the data that we got from our norm dot dist to our actual data and look at the difference between the two now we plotted this information over here this is the actual data plotted in the format of a histogram we did the bell curve in terms of a line charts that gives us the nice curve but it's not an area graph and then we did the area graph of the of the bell curve as well so now we're going to get a little bit more fancy with our graphs and ask a couple questions with this data so let's go back on over here and I can say now now that I have both the p of x in the format of percentages and the total here in the format of percentages I'm going to go back on over to this graph which was my line chart and say is it could I plot those both on the same graph over the top of each other to see how closely the the perfect bell graph fits over our percentages of the total I believe we can so I'm going to pull this graph back out I'm going to select it I'm going to go into the chart design up top and say let's add another column to it selecting the data and I'm going to say we want to add another column if we if we may and this is going to be the percent of total data set and then here you got to be careful with the second one delete what's in there and then select your data we want this data right here control shift down and then I'm not going to hit control up because it's finicky so I'm just going to say do that and then okay and hopefully it picked it up so I'm going to say okay and then I'll scroll up this way and so there's the actual now if I hit the plus button I might need a legend now and so I don't need to let I'm already a legend and I'm already here okay I'm not talking about you as a legend I need a lead to the key the lead to tell me the two oh okay thought you're trying to say I'm not a legend in my own in accounting but any case so here we've got the actual curve versus the the the data that our actual data so you can see they kind of approximate each other so that's another indication which is useful to say okay how closely might this approximate with a big pictorial representation alright so the next we're going to get a also more fancy now with the smooth bell curve so once we once we think the bell curve has some predictive power we can get a little bit more fancy with the actual area curve that we created in a prior presentation I'm going to move the rest of these curves over here these are taken second fiddle seat they're not as important at this point in time put them backstage so that so that the show can go on on the front stage so then another so one of the questions we might ask let's go over here and do it this way we're gonna say okay if if we have this data one of the questions we might ask is well what if X was equal to X is the test scores you'll remember and what's the likelihood that I get a test score of 80 now I can look over here and say well I graphed this whole thing out and and I can say okay well down here I can I can go to my 80 and say there it is so 3% if I double-click on it that's the good old norm dot dist of the 80 and they gave me the 3.48 notice that I represented the 80 again as a whole number so that in this is represented basically as a percent so I'm not represented the 80 is an 80% here and so on so you have to be a little bit careful on that but so there we have it but usually you're not asking that question you're probably going to ask what's the likelihood that I get an 80% or above or an 80% or below those are kind of the questions that you might ask and because it's an area under the curve we might not be able to just kind of sum this whole thing up you might say well last time when we did like the poisson and stuff we will able to sum this up that might give you an approximation about 70.99 but possibly not the exact number so we can then do our questions over here to answer those type of questions so let's say we're going to say I'll type in the operator because I'm also going to put some fancy formulas in here operator and this is going to be the orange which will make sense when I get to the graph and say that this is going to be less than or equal to so if I'm using less than or equal to I'm going to say this is and I'll make this orange this will make sense when I make the graph home tab font group drop down I'm going to make this orange and then I'm going to say okay so so now my question is P I can represent it this way is equal to X is less than or equal to equal to 80 so P of X is less than or equal to 80 let's say so now if I want to change these titles it could be useful to do that it gets a little confusing but once you do it a few times you can make this title dynamic changing the 80 for example to tie into this 80 so to do that I'm going to double click on it I'm going to go to the front of it hit equals to make it a formula I need to put quotes around anything that is text so I'm going to put a quote and I was going to put this cell to put the operator in but I'm just going to leave all of this all the way past the equal as just text so I'll keep that as just text and then that 80 I want to play I want to replace it with this cell that's the dynamic part to do that though I need to tie it together with an and so the and is not an and function it's just tying together the text I'm going to select that 80 and then replace it with this which is G 10 and then I need another and to tie it to the next text which is just that closing of the brackets quote here and a quote after the brackets now that's tedious to do but once you do it it's pretty neat because now you can create these titles that are dynamic so now I can do this my same norm dot disk but make it cumulative to get to get this calculation so this is going to be and we'll see this on the graph as well because the graph is quite useful but it's a little bit tricky to understand how to set up a graph in Excel so it so it does this automatically which is great to do for those of us that aren't good with handwritten graphs right so it's so if you can figure this out in Excel for us non artistic people they can't barely draw a straight line this is a good good tool to pick up so we're going to say this is going to be equal to norm dot disk tab the X is now going to be this 80 the mean comma same thing we did before is that means seventy four point nine two comma the standard deviation is now going to be this ten point oh nine and then comma now it needs to be cumulative which is going to take the area under the graph right under the line up to 80 and so now we want to be one or true true you can type in true one is easier to type so I'll put a one and then I'm going to percentify that home tab number groom percentify add some decimals comes out to the 6926 and you'll recall that that's different than if I went down to this 80 down here and I'm saying there's the 80 there's the likelihood that I get eight here's 80 80 exactly and then if I was to add all this up this would be seventy point nine nine which is close to the 69 point two six but not exact due to the nature that we have a curve kind of situation here so remember this 80 represents the score which would be in percent you know you think of an 80 percent that we get an 80 on it which we're representing as a whole number this 69 point two six is the likelihood that we get an 80 now you might be thinking shouldn't the likelihood that I get an 80 percent be like 80 percent because that's kind of the point but but no you know we're doing it based on the actual you know the actual results which are actually hovering around seventy four ninety two at the mean with a standard deviation of ten around ten right so that so once you have that you could also get into the the Z now the Z is a representation of the number that's in relation to the midpoint the stand the the the mean right so we're trying to represent the number in terms of standard deviations so in other words if I have an 80 here the question is how can I represent that as a Z score so I could do that by saying this is going to be equal to brackets because I got a subtract and then I'm going to do a division so it's going to be the 80 minus the midpoint so notice we're talking about difference from that middle point closing up the brackets divided by the standard deviation right so we're taking the score that we're looking for minus the middle point divided by the spread number the standard deviation so I'm going to say enter and we get a Z if I go home tab number of point five so now we're representing this in Z scores and now if I do this same I could do a similar calculation that we had before but instead of having X now we've got the Z so we can use a formula such as this this equals norm dot norm dot s dot dist so it's a slight difference here right and now I need the Z instead of the X because now I have the Z and not the X and then I just need to say whether it be cumulative notice I don't need the mean and the standard deviation why because they're already in the Z score because we calculated the Z score with the X and the mean and the standard deviation so all we need to do is say is it cumulative or not and I'm going to say it is cumulative with a one and we should get to the same number home tab number group percent adding some decimals of that 69.26 now how can we graph this on the graph if I look at the graph I'd like to say okay I'd like to see if something populate this area over here and so it shows me pictorially at that 80 you know at the 80 so it shows up over here so I could say okay but to do that I can also add the Z let's first add the Z in our list of data I'm gonna say this is my Z column home tab font group numbered or bucket black white center and then I'm gonna calculate the Z for everything here so I'm gonna say okay the Z you'll recall equals brackets I'm not gonna do a spill array function I'm just gonna do the good old normal format this X minus the mean F4 so it's an absolute dollar sign before the G and the 2 closing up the brackets divided by the standard deviation this is outside of my my what I'm currently working in so I'm gonna F4 it because I don't want it to move down dollar sign before the G and the 3 enter there's my Z score let's add some decimals home tab number group couple decimals and double click to drop it down so this is the Z score that's representing below the mean right and when it gets to 0 that means that means it's gonna be around that 74% right so because that means we're at that middle point for the Z score and then this is representing in terms of an essence standard deviations above the standard above that mean point right all right so there's our Z and then if I'm trying to graph this then I can say okay what if I try to put another graph on top here that represents my data up to this point less than or equal to up to that 80 so I could say all right let me see if I can get a graph that's gonna that's gonna be equal to in essence this argument and so now it's dynamic and that I can change that 80 again and I can go to the home tab font group percent and white and let's center it as well okay so now I can say all right let's see if we can use like an if function to to pick up our data here so I'm what I'm gonna do is I'm gonna say I want you to put something here if this number is less than or equal to 80 and if not I don't want you to put anything here so it'll just be blank and then I can graph that on top of this graph and it will give me it'll give me that that another line which will which will be nice visual so I'm gonna say equals if this is our if logical test I'm just gonna say all right I want you to say if this number is less than or equal to less than or equal to this number then that's a comma so now we're going to the next argument what do you want to do if it's true if it's true then I want you to pick up the P of X number but if it's not which is a comma I want you to just put a blank I want you to leave it blank which is just double quotes because it's it's nothing in it so that's our text field with nothing in the middle so that's our argument so I'm gonna say okay and it comes out to zero here because it's less than this it's a percent let's percentify the cell home tab number percentify add a couple decimals and then I'm gonna double click the fill handle and copy and it copies it down now hold on I can see something went horribly horribly wrong here let's double click on it what went wrong notice that this cell is it got copied down see so so I'm gonna absolute reference that one I'm gonna go into that one I'm gonna say okay that's G 10 right there F4 dollar sign before the G in the 10 that sticks this one solid so when I then double click on the fill handle again then it copies it down so now if I go down it'll copy it down to here and then everything over 80 there's nothing in it so now what I can do is I can say okay now I'm gonna go to my graph and I'm gonna pick up this column and that will give me that a different color that will give me that that distinction so let's go to the charts up top and then I'm gonna go to the data and I'm gonna say let's select the data and say I'm gonna add another data set and I'm gonna call the name of it this right here so I'm just gonna say that's the name and then remember be careful with this bottom part delete it make sure you're picking up the right data control shift down and it picks up all this data even though there's blank cells down here okay I'll break it all the way to the bottom because of course they won't be blank if you it's a dynamic thing if you change it okay and then we're gonna say okay and then boom so now you've got now you've got your graph that gives you around the 80 and you could see this the blue line which which is nice to see pictorially so we're going from orange we want less than 80 so the orange is what we're looking at everything less than or equal to 80 and then of course you might want to legend now the legend I don't need a legend because I am I am the legend okay okay calm down calm down so in any case so we have that now that's the now now this gets a little bit tricky because you also might be asking questions like well what if it's what if it's greater than or equal to well that's kind of the inverse so this graph kind of shows the inverse as well so you can kind of use the same graph or you can put up different graphs to give you this middle point and we could also get more fancy with this graph adding the Z to it give it given us the standard deviations down below which is pretty neat as well so we'll dive into that in more depth continuing to work on this in future presentations