 Like each point starting with 65.78 minus the mean 67.99 divided by the standard D 1.9. That's going to give us the 1.16 negative about. We can do this all the way down. The next data point would of course be 71.52 minus the mean of 67.99 divided by the standard D 1.9 and that's going to give us 1.85 about. We can do that all the way down. We can do that all day but we don't have to do it all day because we did it in Excel which means it took a very small part of the day which is nice. That's what Excel is for. That's what it does for us here. So then we can do that with the weight as well. We can say okay here's the data point 1.12.99 calculated in the Z subtracting minus the middle point 1.27.08 or the mean or average in other words and divided by the standard D which is the 11.66 to see what it will be. We get the 1.21 and we can do that all the way down of course. Let's do one more for the fun of it because I'm having a good time with this 136.49 minus the mean 127.08 divided by the standard D 11.66 and that's going to give us the .81 about and we do that all the way down once again or we let Excel do it all the way down otherwise we would go crazy as people must have done before Excel. Everybody before Excel must have been crazy. They're all crazy people. So if we multiply these together we get 1.4 and then if we multiply these together the Z's are 1.85 times .81 that's going to give us the 1.49 so now we're just multiplying together each of them the two Z's and that will give us all the Z's. Now we just sum that up and it gives us the numerator of our calculation for the correlation. Let's do that over here. We're just going to sum up this outer column that comes out to 12,570.96 the denominator is going to be n minus 1. Now this is how many rows we're in this data set so that's telling you how long the data set was it's 25,000 points on the data set which for us is a lot. That's more than we've seen before and then we're going to subtract 1 from it which seems awfully insignificant but it's not because it's our correlation calculation that gives us the 24,999 and then we have the numerator and the denominator if we divide the two out like we're supposed to do when you have the numerator and denominator you take the top 1,1,2,570.96 and divide it by the bottom one the denominator 24,999 and that gives you the .5 correlation. So we have a correlation of course it's not a perfect correlation but we've got the .5 correlation. Now we can double check that in Excel by going to our data tab, going into our analysis using the data analysis if you didn't have that in Excel which it's not on by default you can turn it on by going to the options. We show you how to do that in the Excel practice problem but then you can just click on the correlation you can then go to this correlation select your data ranges which would be the two data sets that we looked at for the white weight and height they have to be together like this side by side in order to select them here so you just pick up the ranges put it somewhere on the output on where you want it to go and it'll spit out the the answer so you got the height and the weight .5 so it's a not a dynamic calculation it doesn't move when the data set changes but .5029 gives us your double check or a preliminary look at your data alright let's do a little bit more analysis on the other thing we can do is you could you you could use our data analysis and you could use the descriptive statistics which will spit out this descriptive statistics as well so you put in the range again and then I'm which I selected the summary statistics and the confidence levels and it'll spit out your statistics like this so for our two data sets we have the height and the weight now again here these are not dynamic meaning they're not formulas they will not change as you change the data but this is a great tool if you have your data and you want to give a preliminary analysis and let it spit out some information about it which might help you to then do whatever you want to do like construct your workbook or use it as a double check to check that your calculations are correct in here so it gives you the mean the standard error the median the mode the standard deviation sample variance kurtosis skewness range minimum maximum some count there's that 25,000 again and the confidence level alright let's now focus a little bit more on the Z scores again so here's our data sets again we have the height and the weight and we will note that when we when we think about the calculation for the Z's for the correlation let's go back over here look at it where did it go don't day these two are the Z scores so really we're comparing the Z scores that's that's the you know the key component that helps us to compare the the data sets and do this correlation calculation so just to get a more of an intuitive sense on what's going on let's kind of focus in on those Z scores a bit more so i'm going to say alright here we've got the mean again here's our mean our standard deviation of our data sets and now i'm actually going to plot this out because i think it's easier to kind of focus in on the Z scores when we have the perfect bell curved shape so we noted that both the height and the weight had a bell type shape so now i'm going to make a curve which will be the perfect you know curve that will simulate the bell shape we talked about this in prior presentations and then we'll focus in on the Z scores from those now remember you don't need to have a bell shape kind of set of data in order to do the correlation calculation not all data sets are going to conform to a bell curve and things that aren't a bell curve could still have a correlation between them but we noted that these two happen to have a bell curve and i think the actual curve will help us to focus