 Statistics and Excel. Perfect positive correlation 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, but we started in a prior presentation. So if you're using a blank worksheet, you may want to begin back there. If you do have access to this workbook, 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. And the 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're thinking about correlations, different or two data sets in seeing if they have a mathematical relationship or correlation between them, meaning are they moving together in some way, shape, or form? If there is a mathematical relationship or correlation, the next logical question is, is there a cause and effect relationship? And if there is a cause and effect relationship, the next logical question is, which is the causing variable in the relationship and how is that working? So we're thinking here first about a perfect positive correlation, which you don't find so often in nature because usually when looking at two different data sets, they might move in somewhat alignment, but not perfectly in alignment. We want to start with a perfect alignment situation just to check that out. So we're using a conversion scenario going from inches to feet. So what we first did is look at our data set, construct our data set according to randomly generated numbers in accordance with a bell curve or normal distribution by using the data analysis. And so and just remember that it's not always the case that you're going to have your your first data set that conforms to a bell curve. So it might if we're measuring like the inches of something like a snake or something. And then you would think that it might correlate to something like a bell curve, but you might have random numbers or some other distribution of data. It doesn't really affect the correlation. But it's useful to kind of see that we could still see those relationships of that we can approximate with the curves like a bell curve. Then we converted it to feet so we can see the relationship there would be the conversion factor. But if we didn't know that, then we might then go through this analysis to try to look at this data. We first looked at it with the mean and the standard deviation. We can look at the D the difference between the inches and the feet, but that's not going to help us too much because they're in different units, of course. So we then said, let's take a look at the actual the actual inches and feet in terms of a histogram and they look like a pretty similar type of distribution. But of course, the numbers are different. So that looks like there might be a relationship there. We then said, since this happens to be in a bell curve format, let's make a bell curve distribution between the two sets of data. So now we made our bell curve as we've seen in prior sections for the feet and the inches. And we did the Z scores. And we noticed that when I look at the Z scores, I could see, for example, one, let's do let's go to one foot corresponds to the the 12 inches over here and the Z scores the same 141 and 141 two feet corresponds to 24 inches over here at 0.67 and 0.67. So the Z scores are something that we can use for comparison, possibly to see if there's kind of a relationship. So then, of course, we have our formula for our correlation formula which takes into consideration the Z scores. So what we're what this formula is basically saying is when we have our two sets of data, we're going to be taking the first X's minus the mean over the standard deviation and then do the same for the Y's those that's basically the Z score. We're summing all that up for all of the eyes and then we're multiplying that times the Z scores for the X's and the Y's and then divided by n minus one. So when we look at our correlation formula, it takes into consideration the Z scores. All right, so now let's start building this out. I'm going to I'm going to bring this over to the right. I'm just going to copy my data over here. I'm just going to go over here and copy these two cells control C and bring our data over to the right. So we have here so we can work on it and say right click and paste. Let's paste it 123. So I'll paste it 123. So we have it there just the numbers and then I'm going to say format up top home tab number group black and white and then let's center it and wrap it. I wrapped it and then centered it. Okay, so there's going to be our information. So now maybe I don't need rounded anymore. I'm just going to call it inches and feet. Okay, so then let's make this a little bit smaller. So the next thing we could do we might just graph these together. This would be the next lot another logical thing to see if there's a correlation. I could select these two. I'm going to say control shift down and then I'm going to say control backspace to get back to the top and I'm going to go into the insert tab now and I want to go into my charts for scatter, scatter chart and typically we'll use I'll use this first one here and I'm going to say wow the scatter chart will pick up that scatter chart looks like it's a straight line which is a pot which is like a perfect positive correlation. So obviously that would be an indication that there's something going on here with the with the correlation. So let's say this is going to be I'm going to delete the title here and what I really want to see is what's on the y and the x-axis. So I'm going to hit the plus button here go into my trend line not my trend line. I'm going to go into my axis titles and then whenever I have this kind of chart it will pick up by default whatever's on the left and put that plot that on the x-axis. So the easiest way to plot these charts is to put whatever you want on the x-axis which is usually going to be the independent you know we often think of it as like the independent variable although you know you can plot it either way which we'll see in a second but that would be the x. So I'm going to say this is equal to on the title the inches and then this one I'm going to click on it and say that's equal to the feet. So there we have it here and if I look at the trend line of course it's the same as a straight line so we'll add the trend lines which will have an impact later but just to check out the trend line if I go into the more options with it then we have our line if I go to my bucket down here I can make the line a straight line I wanted to make it just a line and then I can make it orange if I want to make it orange there it goes and there's our trend line which is of course right in the middle of all the dots because it's a pop a perfect correlation now just also just realize you could if I wanted to switch maybe I don't maybe I don't know what's the you know we can get into questions as to the dependent and independent variable here which doesn't really make this is just a conversion so we might not know what the dependent or independent variable is but in any case we'll get into that in a second let's say that we want to plot it the other way so that we have the inches on the other axis so I'm gonna select the same data and then let's insert it and I'm gonna insert again for a scatter and so here we have it again let's make it a little smaller I'm gonna delete the header and what I really want are the axis titles so hit the plus button and axis titles this time what I want to have which isn't there yet is I want to have let's do that I always think about the X first I want to have on the X the feet which is not there right now and then on the Y I want to have the inches so so I have to flip these two now the easiest way to do that it would be for me when I copied and paste them to put the feet on the left and then it would have done it automatically but if I don't want to do that or if there's multiple columns I'm pulling from I could then go into my chart design