 Statistics and Excel. Bell Curve. Test score example part number three. 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 that's okay whatever because our merchandise is better than their stupid stuff anyways. Like our crunching numbers is my cardio product line. Now, I'm not saying that subscribing to this channel, crunching numbers with us, will make you thin, fit, and healthy or anything. However, it does seem like it worked for her. Just saying. So, you know, subscribe, hit the bell thing and buy some merchandise so you can make the world a better place by sharing your accounting instruction exercise routine. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.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 might want to go back there if you want to start with a blank worksheet. But if you do have access to this workbook, there's three tabs down below. Example, practice of blank. Example, in essence, answer key, practice tab having preformat of cells so you can get to the heart of the practice problem. Blank tab is where we started with a blank worksheet and are continuing at this point in time. Quick recap of what we have done thus far. We've been creating our bell curve or normal distribution and we're not stopping building the bell curve yet. Why? Because I didn't hear no bell. I didn't hear no bell. We don't stop building the bell curve until we hear a bell for crying. Okay, we probably won't hear a bell like even at the end. But in any case, we started our normal distribution thought process practice problem with a common scenario to both students and instructors. That being, of course, grades. We actually constructed our randomly generated grades, which is a great tool to use in Excel when practicing. Found under the data tab up top analysis. If you don't have the analysis group, we showed how to create the analysis group. Once we did that, we are imagining that we are, in essence, the instructor that has access to all the data for past grades. We took that data and then did our standard calculations with it, giving us the mean, the standard deviation, the median. We noticed that the median is similar to the mean, which is one indication that this data set might mirror somewhat or the bell curve might mirror this data and be a good representation of the data. We then wanted to plot our data using a smooth bell curve. So we said, what's the lowest X and the highest X that are going to be plotted on our curve that would be reasonable? We said if we can capture almost all of the data or the huge substantial part of the data, if we go out for standard deviations, that's where we got to the 34 to the 115, 34 to the 115 on the X, remembering that in practice, of course, you would think it'd be very, you can't go really below zero in terms of test scores or above 100, typically in most cases. But when you think about a bell curve, theoretically, the ends go on indefinitely, right? And also remember that we represented our test scores in terms of whole numbers, as opposed to percentages. We then calculated our norm.dist for each of these areas so that we can then create our graph, which was the blue part of the graph over here by going to the insert tab, going to the charts, and we inserted an area graph, which is this one. And then we said, OK, that's great. But what we would also like to do is to be able to say what would be the likelihood, for example, that we've got 80 or below on a test score. We get anything from zero up to an 80%. We can do that with a calculation as we did here, which is the norm.dist calculation. However, instead of saying, I'm going to have it the non cumulative, we made it cumulative. So this has given us the data or the likelihood of us getting from zero up to 80%, which would be the area of the curve. If you were to think of the drawing drawing the curve, the orange area up to the curve up to this point. Now remember that if you actually look at your data over here, you might say, OK, if I find the 80 on this side, the likelihood that I get an 80, this is whole numbers, but represented in 80%. And then it would be 3.48. You might say, I could just add these up, but that's not exactly the answer. Remember 70.99 because it's the area under the curve, which means that there's basically calculus related to it, right? So it's going to be possibly I could give you an approximation, but it's not going to give you the exact answer possibly. Then we can also represent this in terms of the Z score, which we calculate by saying the number that we want, 80 minus the mean, the middle point, 74.92 divided by the standard deviation representing the spread. And this gives us a number that is in relation to how far away from the center point we are at. And then I can use that also to create the same calculation here, but instead of using X using the C score to get to this 69.26%. So now when we look at our graph over here, we actually graphed this orange area with this fancy calculation, which is highly useful because the graphs are really useful to be able to look at when you're trying to understand what is happening. And for those of us that are not good at drawing graphs by hand, it's very useful, although somewhat tedious at first to figure out how we can basically graph this and draw the line right here. So the way we did this is we did a logical test and we just said, hey, I want to use my if logical test and say that if this number is less than or equal to the point we're looking at, in this case, the 80 that we found over here. Making that absolute. Then I want you to give me this number. And if not, I want you to give me just blank double quotes, just nothing. And so that's why it graphs this stuff down to that point where it's 80. And then there's nothing after that point. So when I add that second graph on top of this one, you can see now the orange part of the graph is just kind of overlaying the blue parts basically on top of it, and you get that nice distinction. So now let's do a little bit more with this distinction because that could be a useful tool. Now, the other question that we might have is saying, okay, well, what if if I'm looking at an operator of less than. So we're now we're going to say less than and I'm going to make this blue now because this is going to be represented by the blue part of the graph. Fond group bucket. Let's make this like a blue. It's not the exact same color blue but a blue. And then I'm going to say, well, if this is rep if the orange is representing the the amount up to the 80, then the blue is going to be the inverse right. So that's going to be P of x is going to be greater than the 80. So I can say okay. And of course I can do this by just saying this is going to be equal to one minus what I got here. And so if that's 6926 then this is going to be home tab number group percentify adding some decimals the 30.74 and if I add those two up I'm holding control to get the 100 they add up to of course the 100% Now if I was to do a formula for this you can see what's happening here is just like what we saw pictorially if I'm saying well if this orange is everything up to 80 everything over that point is going to be the blue and everything under the curve blue and orange represents 100%. So if I'm using a formula I can use the same formula and just say it's going to be one or 100% minus. Well first I'll just say this is going to be equal to the same thing I'm going to just do the same thing here. And I'm going to say then that this is going to be equal to one one or 100% right minus and then add my norm dot dist norm dot dist. So there it is I'm going to select tab to pick it up. This is going to be running much the same way I'm going to be picking up my x which is going to be the 80. Now notice it's a little bit tricky because this is equal to so that 80 has already been picked up here. So if I'm trying to do the inverse I'm going to say that this I can't say it's going to be greater than or equal to. So I'm going to say minus that I'll pick like a really low number I'm going to say I'm going to say plus 0.0001 right so now it's not exactly equal to here and then I'm going to say, comma, and then the mean is going to be the 74 again, and then comma and the standard deviation is the 10 as before and then comma is it cumulative. We're going to say it will be cumulative so that's going to be a one and then brackets percentify at home tab number group percentify add some decimals and we get to that 70 30 74 again. So if they ask me a question such as what's the likelihood that it's going to be greater than 80, then I can still populate the information up here and now the orange is representing everything less than or equal to and the blue is in essence representing everything over and above that amount. So that's a little bit kind of tricky to use this graph. We could of course make different, different graphs as well. And but sometimes it might be easier to use the same graph. So we'll take a look at some different variations to make this graph on the right to deal with these different questions of less than or equal to or greater than and then of course between would be the next question that would often come up. Now notice if I go up to my formula up top so this is now I didn't make this a dynamic formula. So if I go into this now I can make this dynamic. Let's go into here and say this is going to be equal to I'm going to put the quotes around the text area. So this is going to be from here to here. I'm going to end the quotes and then I'm going to say and so now I'm going to say and to tie it together and then I'm going to replace this 80 with this actual number up top or that G 10 and then and to tie it together to this last piece of text bracket or quotes and quotes. Alright, so I'm going to say enter. And so now we have a dynamic field. So if I made this go down to down to 60 for example. Now you have the 60 over here and you can see it reflected in the graph and you can see it change in our dynamic field. So let's bring it back up to 80 and then I'm also going to say this let's say this equals equals this one and then I'm also going to tie it together. So I'm going to say and and then I want to tie it together with this one. So now and let's see if I can wrap that home tab alignment wrapping the text. So now this this column is kind of representing those two things which can help me to basically visualize either of those questions with the orange and the blue is the general idea. And I can even adjust my legend over here if I wanted to by going to the chart group. I can go into my data and I can say that this one right here the P of X. Let's go ahead and edit it and then in the name up top. I'm going to change the name to be like this was the blue one. I'm going to say this is going to be P is greater than or equal to 80. Hopefully I get this right and I don't and then this one. I do get it backwards and it's going to be edit and I'll change the name here and say this is going to be the P is less than or equal to 80 and then OK and OK. So now I'm going to go back on over and now we have the legend of the blue is the amount above the 80. And so so now we can kind of ask both of those questions when you have and you can kind of play with it pictorially. OK. So then of course you might also get a question of something being between let's do the Z score first though. So I can do the Z in the same way. Let's just calculate it again so we get an idea of the Z again so this is going to be equal to I'm picking up the number we want. I'm going to say is X is 80 minus the middle point closing up the brackets divided by the standard deviation divided by right here I saw the divided by and then standard deviation. So there's our formula. I'm going to say enter add some decimals home tab number group decimalizing it. So there's our point five once again. And then again I can do this. I can do this same calculation but now with the Z score. So this is going to be equal to norm dot s dot dist and then enter. And so now all I need is the Z because the Z you'll recall just as we saw is taken into consideration the mean and the standard deviation in that calculation. So I just need the Z now comma it needs to be cumulative and notice I didn't put the one minus. So it's going to be wrong. But let me just show you I'm going to say this is going to be true. Close it up. And so if I percentify that I can say oh wait a sec. If I put those together that adds up to 100. This is the inverse I got to this I need to say one minus I'm going to double click on it and say this is going to be one minus 100 percent minus that and then I get that same number. Now the next question that we get is as you can say that this is going to be P and let's say let's say that that the lower the lower is now going to be let's say 75 in the upper is going to be what we had up here which is just going to be the 80. So now I'm going to say between the lower and the upper is what we want. So we might put that in here by saying P is going to be and you could represent it this way like 75 is less than or equal to the the X which is which is less than or equal to the 80. I kind of like doing this I don't I know this may not be that I tend to think of it as X is going to be is going to be less than less than or equal to 75. And then I typically say and and it's going to be I'm sorry X is going to be greater than or equal to 75 and less than or equal to 80. I tend to do it that way that might not be the best way right that's the way I kind of think of it usually. So so if you wanted to put so so there's now I could put make this into a dynamic thing again I can go into it and say this is going to be equal to quotes around this quotes after this bit and then that 75. I'm going to say and to tie it to the 75 I'm going to replace the 75 with this 75 and then that and is actually something I want as a text field. So this gets a little bit weird because now I have to say OK this is going to be tied to with an and and then that and I want as text so I have to put a quote around the and and then I'm going to go all the way from the and to the equal end quote and then I need an and this is not a text and but one that's going to tie together the 80. I'm going to replace the 80 with this cell and then I have to tie that together with another and and then the text at the end is going to be quotes around this last bit and enter. So that's so now I've got this dynamic thing where I can change the 75 to 60 if I wanted and it changes basically automatically down here put it back to 75. Now if I wanted to do this then of course using the cumulative ability I can I can take this all the way up to the 80 as we did here which would be represented by the the orange and then subtract out everything up to the 75. So I'm going to say all right that means I have to do so I could I could do it this way I could say this is going to be equal to. I had I had. Let me let me hold on I got confused there for a second this equals norm dot dist so I'm going to pick the larger one which is going to be this 80 and then comma the mean up top is 74.2 to 92 again comma. So I just hit comma so you can see it down here and now I'm on the standard deviation that's going to be the 1009 again and then comma and then cumulative we do want to be cumulative therefore one or true closing up the brackets and then I have to subtract out everything up to the lower point which will give us the middle stuff. So norm dot dist again the X now is going to be the 75 comma the mean once again is 7492 comma scrolling down just so you can see the argument. So now we're on the standard deviation scrolling up 1009 and then comma this also cumulative true or one close up the brackets and percentify home tab number group percentify. Adding some decimals and we get to the 1895 so notice that you have to be careful with the equal signs. Whether it be greater than or equal to now you might say well if I look at this I can say if I go to 75 here's the likelihood they get a 75 up to 80. What did I say 80 I can add those up I get to 18.7 remember that's not exactly the same here because it's the area under the curve so it's going to be like calculus and whatnot so it's a little bit different. So this will just be that let's just put the same thing here. We could do the same thing with Z's so I could take the lower Z and then the upper Z and do it with the Z scores the upper Z. So now I'm just going to convert these two to Z scores. So I'm going to say this is going to be equal to brackets 75 minus the mean which is 7492 close up the brackets divided by the standard deviation which is 10.09. Decimalizing home tab font group decimalize and so there's our Z and then I can do it here this is going to be equal to brackets. We're going to pick up the 80 minus scrolling up will pick up the 74 closing up the brackets and then dividing by that's not a divide dividing by and we'll pick up the standard deviation. And then enter decimalize home tab font group decimalize and so remember that the closer to zero that means it's you know in the next to the mean close to the mean right. So now I can do my I can do my same kind of calculation with the Z scores. So this is going to be once again this this will be equal to norm dot s dot dist and now we're going to take the Z I'm going to take the one related to the higher point which is the upper Z. So this is going to be this one comma it needs to be cumulative therefore one close up the brackets minus norm dot dist not I'm sorry dot s dot dist tab the lower Z comma and then it needs to be cumulative therefore one or true and close it up percentify home tab number percentify brackets and we once again get to this one. Now this this is a little bit harder to represent with just our one graph over here so obviously I can see that upper limit of the 80 and then you can use the same one to visualize the lower. By simply changing this to the lower is 75 right so I can change this down to 75 and I could kind of go back and forth in my graph and say OK it was between this line and then where it was before which was the 80 like when I went up to 80 over here. So it would be nice if you can make a graph that shows both of those which will do in future examples but you can use this one graph to get some visualization of all of that together. Now let's play with our graph over here a little bit and see if we can make it a little bit more a little bit more fancy. You might think it's impossible that's fancy as fancy can be already but no will add maybe a Z score to it which will totally up the fanciness a lot. So notice down here we've got our our our test score amounts and the middle point is over here we've got this line. We might also want to be representing another X with our Z scores so that we can also see this graph in relation to our Z scores. Let's make this one a little bit smaller and then I'm going to say so so I want to add that to my graph. I want to add another X down here so I can use either X and refer it to the Z score or to the actual numbers. So to do that it gets a little bit tricky little bit craziness happening here. I can say OK if I go into this one well hold on before I do that if I click on one of these items and then let's just double click on it and that will then open up this field. I'm on the format data series. I'm in this one to the item with the right with the bars and we have the primary axis versus the secondary axis. What we want to do is add a secondary axis. So I'm going to add that and then I'm going to close this back up. It will often put these numbers on the right hand side. I don't need that for this particular graph so I'm just going to remove them clicking on these numbers and deleting them. Now that that is in place I can then go up to my data which is in the chart designs and then the data the data group select data. And what I'd like to do so notice that this right here is is being tied to this series of numbers. What I want to do is I'm going to pick the second data set and notice I'm going to change another I'm going to add another X. I'm going to make it different. So I'm going to say select this information. I'm going to hit this and then go on over and this I want my Z score. So I'm going to put my cursor on the Z score control shift down make sure that it picks up the right number. So I'm going to say OK and OK. Now notice sometimes it kind of messes it up here. So make sure that this is listing all the numbers doesn't just have one at the top and then I'm going to say OK. And if I go back up top again now I don't see any change like what happened. Well I've got to pull in my secondary one here. I'm going to hit the plus button on the right and then my axis is going to hit the arrow on the right of the axes. And here's my secondary horizontal which I now have the option for. So I'm going to select that it normally I'm going to hit the plus button to get rid of this stuff on the side. It normally happens at the top. So it puts it at the top. I want to bring it to the bottom. So if you double click on this then I'm over here on the right hand side of the format axes. I'm going to go down to the labels and then under the labels we got the label position. I'm going to select the position and bring it low. I want to bring the position low. And so there we have it. So now I'm going to go into this. So now I can represent this by Z score as well as by the this this number. So if I was to look at this calculation then you'll recall that I had had the probability being. This is the 80 so representing the 80 right here. And then when we calculated the Z the Z was at 50. So you can see the Z that the middle point of course is where the Z is going to be zero. And so then we're up to 50 right there is going to be that line. So now I've got the two X's and I can represent this graph either way whether I be talking about Z's which you'll remember is is how close to kind of. The center point the spread of the items and then and then the actual number values the X's that we have here. Now you can also kind of play with these Z scores like you might you might want to put the distance between the Z scores equal to like a standard deviation. So you could try to adjust the spread of the Z's as well as the X's. I'm not going to get into that in detail right now let you kind of go in like you can you can play with those on your own and say you know what should be the intervals be between the Z's. And the X's if you want to get a little bit more detailed there but I'll leave the defaults for it for now. So what we have now is somewhat of a dynamic type of chart. So here's all of our data up top if I wanted to based on this information to change the the test scores. Then of course I can do that and I can say if I bring this down to 60. Then for example now we're at the 60 the orange representing P of X is less than or equal to 60 which would be the area under the orange area. And obviously the inverse when we're talking about P of X is greater than 60 is now being represented by the blue area. And if you were to get to the area it should be calculated up to it should be equal to 93.04 you know percent of of the the area under the curve. We can also represent it with the Z scores. So the Z score is going to be that line right there right which is now according to this at the 148. So 148 looks about right that we have the the Z score right there. Let's do it. Let's bring it up to like 65. So now we brought it up there the Z score is around 0.98 which is right around here right. And so again you can kind of adjust let's make this as large as we can to play with it. So so then we've got the Z score here they put at the 0.98 and we can adjust this. Let's put this up to like let's put it up to 90. And so now we've got most of it being orange representing everything that is less than or equal to 90 right. And then we've got the everything above is the 90 and above for the blue. And then the Z is at this 1.49 which should be represented you know right here 149. Looks like it's basically boom it's a little a little twisted on the way down but there that is. So that gives you kind of your pictorial representation. Now in future presentations we'll see how we can kind of make a graph that might be more specific to questions that are greater than. And then there is a way that we can do it between two. But just to get one graph that can give you kind of a pictorial representation of all this stuff. This isn't a bad one to work with. So I'm going to now do some formatting of everything we've done. I'm just going to make this data. I'm going to select the whole thing and do my normal formatting which is home tab font group. I'm going to make this blue if you don't have that blue more color standard. I use this blue OK and then drop down and make it bordered. I'm going to make all of this blue bordered and blue. I'm going to make this blue bordered and blue. I'll put some borders around this one but not blue. And then this I'll make all of this blue and bordered as well border blue. This one I'll just make bordered and then I'll make all of this bordered blue too. So to control shift down and I'll say border blue on that one. And there it is. We'll work a few more of these examples in future present. Let's put some borders around this for fun. Just put some borders around our starting data.