 Statistics and Excel. Bell Curve Test Score Data Example. Got data? Let's get stuck into it with statistics and Excel. Although we'll be using OneNote this time, but we'll be talking about Excel. You're not required to, but if you have access to OneNote, we're in the icon left-hand side. OneNote presentations, 10-16. Bell Curve Test Score Example tab. We're also uploading transcripts to OneNote so that you can go into the View tab, take a look at the Immersive Reader tool, change the language if you so choose, and then either read or listen to the transcript in multiple different languages tying into the video presentations with the timestamps. OneNote desktop version here in prior presentations. We thought about how we can represent different data sets using both mathematical calculations such as the average or mean mode, quartiles, median, and so on, and with pictorial representations such as a box and whiskers or histogram. The histogram being the primary tool we use to envision the spread of the data, and we can use descriptive terms about the spread of the data on the histogram such as the data is skewed to the left, the data is skewed to the right. We then thought about certain curves and lines that we might be able to represent using formulas that could give us an approximation of the actual data. Whenever we could do that it would be a great thing to do because that gives us more predictive power generally into the future over whatever the data is representing because we have now a formula for it. So some of those curves and lines we've talked about in the past are a uniform distribution, binomial distribution, the Poisson distribution, the exponential distribution. Now we're looking at the most famous one of them all, the normal distribution or the bell curve. So the bell curve like all of the other types of distributions is only going to be useful in those cases when we're looking at certain types of data that conform to a distribution like a bell curve. So we have the same kind of process we might go through such as looking at the data, testing it to see whether or not it looks like it conforms approximately to a bell curve. If it does, then we might be able to use an actual bell curve to give us that kind of predictive power over the data. Now many things of course do align to like a bell curve type of shape. The bell curve, I think one of the original ways that the bell curve came about was actually to test errors in approximation. So when they were trying to see things in the stars, for example, and see where a star is going to pop up or a planet's going to pop up or something like that, they would try to make mathematical representations about it. And of course they would all be off, but they would have a tendency if you average them together to be off in a normal distribution around the correct answer, which is quite interesting. And it does give a lot of weight to the idea of how you might structure certain types of things. So if you're trying to find something in the ocean or something like that, for example, instead of getting in a room and having everybody kind of come to an agreement, you might have everybody actually do their own calculations and then try to figure out where the thing is in the ocean. And then you put all those calculations together and average them. That might be a more better way to find an answer in some cases. So it's kind of interesting. But a lot of things in nature also kind of conform to a bell curve. So whenever we're talking about heights of things like humans or other or animals or trees or things like that, you would think that they would basically be conforming to a bell curve. Most of them would be around the middle and they would have a bell curve distribution out. You could do that for heights in general, for heights of males and females. You could do that for weights. For example, you can do that for for things like calorie intake, you would think that we would hover around some midpoint and go above and below that from time to time, but not too far because otherwise we would get really big or really small. You would think so there's many, many things where a bell curve might approximate the data. However, it does not always approximate any data set, which is sometimes a misconception that people have. They think that everything kind of should conform to the bell curve, and that's not necessarily the case. We run into the same issues we saw with the prior curves. The data set could be doing anything. It could be doing something very chaotic, and we don't have any curve that can approximate the data. So so what we want to do is determine if the data can be approximated, if it can then apply. Now oftentimes, test scores, of course, might be something that conform to a bell curve, because you have a similar kind of thing. If you have a bunch of people that are somewhere in the same area, the same grade or something like that, or they're in the same level of education, you would expect that the results that they have might conform somewhat to a bell curve type of distribution. And that's of course, also, one of the most common examples, both for instructors and students, as they're always always kind of analyzing the grades. So let's imagine we have this information for grades. Now note that in Excel, you could generate your own data in real life, we're imagining that the instructor has the data has compiled the data, and is now looking at the data to see if it conforms to a bell curve. In practice, you might say, How could I get this data? Can I make up this data in some way? You can't really just use a random number generator, because it's not going to be completely random, it's going to be in accordance to a bell curve kind of distribution. There's a tool in Excel that allows you to do that, though, and it's in the it's in the bar up top, under the data tab, so it's under data. And then you're looking in the analysis, and it's data analysis. Now we turn this on when we work this in Excel. So if you want to work the Excel problem, you can check this out. If you don't have this analysis tab in Excel, then you can go into the options, the file tab and the options and turn on the data analysis, which again, we do in the Excel practice problem. But great tool to be able to practice with these bell curves and some of the other distributions, so that you can generate your own data that you can practice with, which is great. So in order to generate that data, we need to know the mean and the standard deviation. In real life, we might not know the mean or standard deviation. If we were the instructor, we would just be picking up the data and then running the analysis on that information. So here's our random generated test scores. Now note that the test scores, I purposely made them not in the format of a decimal or a percentage, but rather in the format of 90.97 representing a 90%. Right? So and that makes it a little bit easier sometimes when we do the the norm dot dist calculation. So in other words, if you're working with a data set that is represented in decimals or percentages, you might want to multiply it times 100, so that you end up with a whole number of representation, because then you'll have percentages when you do the norm dot dist. So just to point that out, that's what we did here. So the 76.26 represents the 76% and so on and so forth. So here is all of our data. So then based on that data, we're imagining in our scenario that we're an instructor that has collected this data over multiple years or something like that, we can take the mean or average of that data. And that would of course in Excel be the trustee average function equals average of all of this data, we just sum up or take the average of it, and it comes out to 74.29. Now you might say why isn't the average exactly 75 because I used 75 to generate the data in Excel. And that's because there's randomness involved in it. So it's going to come around something close to that midpoint of 75. But it's got the randomness. So it's not exactly 75. It's 74.92. And then the standard deviation, which would be the standard deviation here of the population is what we're using at this point in time of these numbers gives us 10.09, which once again is not exactly the 10, which we had to use to generate the