 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. 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-formatted 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 in 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 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 34 to 35 or 35 to 36. And then we did a percent of the total of the actual data, because this then approximates 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 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 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 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 to let to the key the led to tell me the two Oh, okay. Thought you were 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 are 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 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 back stage 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 going to 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 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 as an 80 percent here and so on. So you got 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 were 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 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 then 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 and excel so it so it does this automatically, which is great to do for those