 Statistics and Excel. Correlation, simple, few data points, example. 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'll basically build this from a blank worksheet. But if you do have access, three tabs down below, example, practice blank, example, in essence, answer key. Practice tab having pre-formatted cells so you can get to the heart of the practice problem. The blank tab, blank worksheet so we can practice formatting those cells within Excel as we work through the practice problem. Let's go to the example tab to get an idea of where we will be going, what we will be doing. We're looking at correlation, once again, two different data sets to see if there's a mathematical relationship between them. A correlation, in other words, if there is a mathematical relationship, the next logical question would be, is there a cause and effect relationship? And then the next question would be, what's the causal factor in that type of relationship? We're going to use an example this time, which is not a perfect positive or negative correlation, which is going to be similar or more like what you would see in practice, meaning the data sets might be moving together, but they're usually not going to be perfectly correlated in either a positive or negative kind of scenario. We'll start this one off with just a few data points. We're going to be imagining we have hens and the number of eggs that the hens will be producing as our data points. We're just going to have four of them. We'll take a look at our correlation formula and calculate that. And then we'll also do some of our statistics using our data analysis toolkit. So let's go to the blink tab and start, let's get into it. Let's get into it. So I'm going to start down here with just our definition. I'm going to say that X is going to be, for this problem, is going to be the number of hens. That's not a number, the number of hens. And then we're going to say Y is going to be equal to the eggs that the hens produce. Now, before I get much further into it, let's do some formatting of the entire worksheet, which is usually what we started. You're getting ahead of yourself, selecting the entire worksheet, right-click, let's format those cells like we normally do every time. We want the currency, negative numbers bracketed, no dollar sign, no decimals. And okay, let's embolden the worksheet by going to the home tab font group and bolding the entire worksheet. Alright, so then given that, let's list our data points. We're just going to list our four points, which will be somewhat correlated here. Now, this might not be an actual data set. We're just kind of making the four points, but you get the idea of it in the example. Let's go to the home tab. Let's go to the font group and make this a header, dropping it down. I'll make it black and then white on the font. Let's center it alignment group and we will center it on the center. And that looks good. So then I'm going to say the data points for X are going to be 3567. Those are the number of hens, the eggs that are produced by the hens. We're going to say 105185. I think this is a year 201 and 345. There's our data points. Let's go ahead and format these. I'm going to go to the home tab fonts group. Let's put some borders around it and I'll make it blue as well. Drop down. If you don't have that blue, I'll hit this one down here. It's in the standard color going to make it blue. You could do whatever you want to do, but I'm going to make it blue. And then I'm going to go home tab font group. I want all borders, not just those borders. Let's do the same thing here. Blue and border, border blue. We can make this a little bit thinner without messing anything up, I think. Let's skin arise, skinnier rise the A. There we have it. I could use some skinnier rise in myself. I'm on a diet, but someone stole my protein shakes. Now I was on my protein diet. Now I can't do it. Anyways, that's another story for another day. X and Y here. We're going to say, let's do the same thing. And I'll highlight these two. Let's go to the home tab clipboard format painter. There's our headers. Paste that on down here. Do some simple calculations. Let's take a look at the mean. Let's take a look at the standard deviation. And we could do the variance. Actually, let's not do the variance yet. That might confuse what we're looking at. So let's pick up the mean. That's the average equals the average tab of the first set of data, which are the number of hens. Control shift down. And we have an average of five of them. Let's add some decimals so we can really recognize the data. Home tab number group. Decimalize it. And then let's go to the average of the Ys. The number of eggs equals the average of the number of Ys. Control shift down. Enter decimalizing it. Home tab number group. And boom. So we've got around five and a quarter hens. And they give us 209 on average eggs. That'll keep me going. That'll keep me going. That'll get me yoked up so I can build some muskles. I don't need my protein powder now because I've got my eggs. So this is, we're going to say standard deviation. I'll just drink them like Rocky does without Rocky Balboa, the boxer without, I don't even cook them or anything. Anyway, standard deviation with the S sample. We'll select this data and enter. And then home tab number group, adding some decimals. And then on the Y, this is going to be equal to the standard deviation. Standard deviation S tab. And we'll pick up this data and enter adding some decimals. Home tab number group. Let's add some decimals there as well. All right, so there we have it. Now we could do our correlation calculation. And we'll use this formula to do so. Before we get there though, let's take a look at just making a graph of this. So I might say the easiest thing I can do is just say, well, there's the X and the Y. Let's just graph this out and see if, and plot it. So we're going to go to the insert tab, select in that data, insert tab charts. We're going to our insert here for the scatter. Let's just make a normal scatter plot of it. Now when I make the normal scatter plot as we have seen, oh, what is going on there? Undo that. I didn't mean, I didn't mean it, man. I didn't mean it. Let's hide that. Let's put this down. Let's make it a little smaller. Okay. Okay. Okay. Everything has now, let's scroll it down a bit. This chart needs to be bigger than this. And then I'll delete the top one. Now you'll recall that the X, the one on the left will be the X that it's applying on this side and the one on the right is going to be the Y. So if I add my labels, I usually start with the X. I'm going to say this equals, it's going to be the one on the left, which is the X in this case, or the hens, right? The number of hens. And then over here, we're going to say this is going to be the Y, which is going to be equal to, I'll just say the Y, which are the eggs. So there's the X and the Y. And maybe that would be better for me to do. This equals the X hens over here. And this equals the Y, the eggs. Because, and then I can say, okay, it looks like there's kind of a relationship, which you would expect. If you're, if you're like a farmer or something, you're trying to buy some hens to get some eggs. You can get some protein because someone stole your protein powder. Then you could like, then you could make some predictions on how much protein you can get from those hens by counting the eggs. So then I could add a trend line, hit the plus button down here and add a trend line. And let's see some options for the trend line. So clearly there's not a perfect correlation, but there is a correlation. I can make a formula for that trend line by just selecting this option, which is nice because that gives us that kind of predictive power. So if I'm trying to buy my hens and see how many hens I need to get so many eggs, for example, then the linear line hopefully might give me some predictive power on like the number of hens or something I might need. If I go to the first tab over here, let's make the drop down. I'm going to make it a straight line and then I'm going to make it orange. That's what I kind of like to do with my trendy lines, which are called the trend line. Hold on, I wasn't on the trend line anymore. Let's hit the trend line.