 Statistics and Excel. Perfect negative 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, 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, it's a blank worksheet so we can practice formatting the cells within Excel as we work through the practice problem. Let's take a look at the example tab to get an idea of where we will be going, what we will be doing, we are once again thinking about correlations between different data sets to see if there's a mathematical relationship or correlation between them. If there is, then the next logical question would be, is there a cause and effect relationship? If there is, the next question would be, what's going to be the causing factor in the relationship? So right now we're going to be focusing in on the negative correlation, a perfect negative correlation. Last time we looked at a perfect positive correlation. Just like with the perfect positive correlation, the perfect negative correlation is not something we often see in nature because we're usually looking at two variables that might be moving together but not be perfectly moving together, but we want to look at that perfect situation first. So some things where that will be the case are things such as you're talking about the distance traveled versus the distance remaining. Obviously, if you're traveling somewhere and you're trying to get to an end location, as you get closer and closer to the location, the distance that you're traveling is going up and the distance that is remaining for you to get there is going to be going down in a negatively correlated relationship. So that's what we'll use with our example to see it. We're going to first create the distance traveled and this time we'll just use a random generator to generate it instead of what we did last time with the positive correlation where we used a normal distribution random number generator and then notice that the random number generators will not give us really a difference between the correlations but we might be looking at data as we saw last time which tends to conform to a bell shaped curve if we were measuring something in nature like a snake or something like that which also could have a positive correlation to a second data set and this time we're looking at something where if you were looking at a curve related to it or trying to approximate the line because we're looking at just randomly generated data it might conform more to a uniform distribution but what we're looking at is to see the the relationship between the distance traveled and the remaining distance so we'll map that out the distance traveled and then we'll calculate the remaining distance we're going to imagine that our distance is 100 miles let's say to get somewhere so 100 minus whatever the distance traveled would be the distance remaining and we'll imagine that we don't know that these two data sets have that relationship right so then our question would be if i had these two data sets could we see the relationship could we figure out the relationship or how can we analyze whether there is one we'll take our mean we'll take our standard deviation we'll look at the charts noting that these charts will be going more towards a uniform distribution instead of a bell curve that we saw last time and then we we will plot out our correlation calculation both kind of working the math as well as using excel to spit it out automatically and we're going to get to a negative one this time because it's negatively correlated exactly all right let's go to the blank tab and let's do it enough talk man that's enough talk let's get down to the brass tax but i don't want the brass tax or sharp don't step on them let's let's take the whole sheet here we're going to right click on the sheet and format the cells and we're going to go to the currency and we're going to go to negative numbers and let's make this uh no dollar sign and start out with no decimals i'm going to go to the home tab font group make the whole thing bold you don't have to but when you're on when you're on camera with a screencast you have to be bold that's what you have to be bold that's what all the greats say in the theater of screencast excel you must be bold so it's like i'm in the great tradition of whatever here we go home tab uh let's go to the alignment center of this thing let's wrap it and let's go to the font group black and white and then let's just generate some random numbers between uh let's do it between one and one hundred we'll just have the random numbers so this is going to be equal to and i'm going to say then we'll have rand between rand between there's our formula we have seen in prior sections the bottom number is going to be we'll say uh should we go to zero let's go just go to let's go to zero comma and the top is going to be 100 so it's going to give us random numbers between zero and a hundred we'll say okay i'm going to copy this one down now so i'm going to copy it down and let's go down to something that's not exactly a hundred let's like go to 200 something to 206 and it's going to be 205 because there's a header so it should that'd be like 205 data points now the random number generator going back up is always going to keep recalculating let's keep reshuffling so if i want it to be solid and not reshuffle now that i have some random numbers generated i'm going to copy the whole thing select in the column control c or right click and copy put my cursor in c and right click and paste it one two three don't you see and there it is and now i can format it let's just copy the formatting for this one home tab clipboard format and then paint that formatting right there with my paint brush i'll make a skinny bee skinny bee that's what they call me sometimes some skinny bee uh over here skinny bee all right so then we're going to say that the distance remaining distance remaining i'm going to copy the formatting over here in in in c uh home tab format painter put that into d so if we're saying the total distance is a hundred miles the distance remaining is always going to be 100 minus the distance traveled so if i travel 94 miles the distance remaining it's going to be 100 minus 94 inverse relationship happening double clicking on the fill handle to pull that down so if i went 65 miles the inverse relationship would be 35 if we went 14 miles the inverse would be 86 and so on so we can see that relationship of course because we made the data set but if we had these two data sets and didn't know the relationship then we might do our analysis here to see that they're perfectly negative correlated which might give us some indication of what is happening with it if we didn't know what was happening because we're imagining that we didn't actually just make it so that we could make it do what we want so we're going to say let's make a skinny e column and let's say this is going to be let's copy the same this is going to be equal to the distance traveled and this will be equal to the distance remaining let's format paint it here selecting these two home tab clipboard paint brush make the same labels up top all right let's do our standard mean calculation this is going to be equal to the average average and of the distance travel control shift down and enter so there's our average we normally