 Statistics and Excel. Perfect positive correlation. 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, there's three tabs down below. Example, practice, blank. Example, in essence, answer key, the 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 cells within Excel as we work through the practice problem. Let's go to the example tab to get an idea of what we will be doing, where we will be going. We're going to be thinking about correlations. In other words, two different data sets to see if there's a relationship, mathematical relationship or correlation between them. Are they moving together in some way? If there is a correlation, the next logical question would be, is there a cause and effect relationship that is resulting in that correlation? And then the next logical question would be, what's the driving factor which of the two sets of data are driving the correlated relationship? And what of course is the cause of that? All right, we want to first look at a perfect correlation, which is something we don't always find in nature because usually when we're thinking about correlations it may not be a perfect correlation. They tend to move in alignment but not perfectly aligned. But let's take a look at the perfect scenario first, which happens with things like conversions for example. So if you have a set of data that are in inches and then you look at another set of data that's in feet, then they're going to be correlated, perfectly correlated together because of the conversion. So what we'll do is we'll actually make our inches data from the randomly generated norm.dist function just to practice that. So we'll have a bell curve kind of information. We will round it and then we'll do our feet conversion. We'll do our mean, our standard deviation. We'll plot it out in terms of the norm.dist just so you can see the relationship that way as well. If you had a set of data that was conforming to one of our distributions we've talked about in the past, such as the normal distribution or bell curve, and then we'll plot out our data set and look at the Z scores and we'll calculate the actual correlation. We'll do it both in kind of a mathematical format within Excel and then we'll use some Excel tools to calculate that correlation and also look at our data set over here that'll give us some standard statistics just to practice with that as well. All right, let's go to the blank tab to start it out. I'm going to select the entire worksheet with the triangle, right click on the selected area and format the cells. Within the cells we're going to say we want currency, negative numbers bracketed and red. Let's remove the dollar sign. I'm going to start with no decimals to start out with and okay. Let's go to the home tab, font, recording. When you're on camera you've got to be bold. You've got to be bold so that's what I'm doing here. I'm going to start to create the generated data so we're going to randomly generate data that we can play with here. I'm going to say the mean is 36. To do this I'm going to conform it to a bell curve. Later on we'll do some just random data but I'm going to conform it to a bell curve randomly generated bell curve data which still has randomness in it. Standard deviation is 15. These are the some conditions we need to have met in order for us to generate this data that conforms to like a bell curve situation. Let's make a skinny C and then I'm going to say this is going to be an inches. I'm going to imagine this in inches. Let's make this our header format, home tab, font group, bucket drop down, black and white on here and let's center it alignment and center and then we're going to go to the data tab. We're using the analytics analysis. If you don't have the analysis tool pack then go to the file tab over here. You want to go to the options down below and then you want to be on general add-ins and then you want the excel add-ins and then go and then tick off the tool kit, the tool pack. It's a powerful pack of tools and then we'll have the data of the analysis and then the data analysis and we're looking for the random number generation, random number generation and okay and then let's say it's going to be a one here for the columns, number of random numbers. Let's make some kind of a more unusual, let's make like 310. I don't like just putting 100 all the time because then sometimes it comes out that we always think of as a sample of being like 100 but in any case let's hit the drop down and say that we're going to say this is a normal, oh hold on K-PASO, normal distribution right there. All right then what do we need in order to generate this? We need the mean. So you need to be mean in order to generate this. So go out and be mean. You don't have to be mean. You need the mean like the average, the average and then down here I want to put this somewhere. Where do I want to put it? I want to put it right there. That's where it needs to go yo. Did you just say yo what is wrong with you? Okay what do you mean yo? I was okay so there we have it and then let's round this inches rounded. So I'm gonna round it now to a whole number. So now I'm gonna say inches rounded. Now we could have generated this any other way. You could use random data and you'll still have a correlation that will be perfectly correlated because we're going to be converting inches to feet but I just want to practice the random generation because we could possibly be looking at a data set that has a bell curve related to it. Say we're talking about inches of something in nature how long a worm is or something or a snake I guess at this I don't know and then we can say it will have some kind of bell curve and then of course when we convert it to feet the feet you would think would have a similar kind of relationship. All right so inches rounded let's go to the home tab font group black white let's wrap it let's center it and I'm gonna round this thing equals round it down. So round I'll just pick the one to the left and then I'm gonna say comma two digits to two digits that's to the whole numbers close it up and boom there it is 16