 Statistics and Excel. Correlation random number generation example part number two. Get ready. Taking a deep breath, holding it in for 10 seconds, looking forward to a smooth soothing. Excel. First, a word from our sponsor. Actually, we're sponsoring ourselves on this one because apparently the merchandisers, they don't want to be seen with us. But that's okay, whatever. Because our merchandise is better than their stupid stuff anyways. Like this CPA thinking cap, for example. CPA thinking CAP, you see what we did with the letters? And this CPA thinking cap is not just for CPAs either. Anyone can and should have at least one possibly multiple CPA thinking caps. Why? Because based on our scientific survey of five people, all of whom directly profit from the sale of these CPA thinking caps, wearing this CPA thinking cap without a doubt, according to the survey, increases accounting productivity tenfold. Yeah, at least. Apparently the hat actually channels like accounting energy from the quantum field ether directly into your head. Allowing you to navigate spreadsheets faster. It's kind of like how in like the matrix when Neo learns kung fu, or at least that's what the scientific survey's saying. So get one because the scientific survey participants could really use some extra cash. 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 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, the blank tab. That's where we started with a blank worksheet and will be continuing at this time. A quick recap of what we've done thus far. We're thinking about correlations, different data sets to see if there's a mathematical relationship or correlation between them. If there is a mathematical relationship that are moving together in some way shape or form, the next logical question would be, is there a cause and effect relationship between them? And if there is a cause and effect relationship, the next question would be, what's the causal factor in that relationship? So we've been thinking about random numbers. We generated two sets of random numbers between 1 and 100. Therefore, you would think you would have a very low correlation between the two given the fact that we generated them separately. They're not really connected other than the fact that there are numbers between 1 and 100 that were randomly generated. Noting that if we made a histogram for each individual data set, they're going to look similar in that they're tending towards a uniform type distribution, because we used the same method to get to both data sets, but they're not actually connected because one is not causing the other. So you would think there would be a low correlation between them. We compared that then to another system where we didn't use exactly random numbers, but we spaced out the numbers a little bit more just so we can get an idea of what oftentimes people envision as randomness, which is actually not exactly randomness because it's usually going to be spaced out more. So when most people envision random points, they often envision something like this where the points have a lot more space, there's a lot less clumpiness, whereas randomness often looks more like this where you have a lot more clumpiness going on. However, note that either system that we used, the two random numbers generated, we have a low correlation between them because they're not tied together, and the other one where we kind of put a system together, still you're going to have a low correlation between them given by the regression line here. So we could see that pictorially. Let's do a similar mathematical calculation for it now. So now we're going to do our mathematical formula and let's start. We're going to say this is our formula right there. There's the formula. Let's pull this one down and put this over here and let's make a long T column so that we don't have all those other spaces and I'll make this one smaller so it fits in the T. That's your space. We made that just for you right there. You have to fit in that space. And then this is our formula. Okay, so then I'm just going to copy my first data set, the two randoms. So let's copy that and do our calculation over here that we've seen in the past. I'm going to paste it in you and control the pasting it. So we're going to do our Z scores again. So we'll do our Z scores. I'm going to insert a column clicking on V and right click and insert that'll insert an entire column. This I'm going to say a Z let's say of one and the Z score is going to be equal to brackets. We're going to take this number each of these points minus the mean. So minus the mean I'm going to go to the left holding down the left arrow till we find that first mean right there. I'm going to say F4 on the keyboard to close and then to put a dollar sign before the H and the two and close it up and then divide it by the standard deviation. Left arrow again looking for that standard D which is right there and F4 on the keyboard. So there we have that and enter. So now I'm going to decimalize it home tab number group adding some decimals and let's bring it on down double clicking to take it down to the bottom. Let's do the same thing for the Z of the second one so Z of two same process. I'm going to copy the format painting here home tab clipboard format painter. Let's put that right there take the Z's of these equals brackets the 27 minus the mean of the second set of data left left left. There's the 49 right there F4 on the keyboard dollar sign before the eye and the two closing up the brackets and divide and buy the standard D going to the left to find the standard D standard deviation. That is but it sounds cooler if it's like standard D the standard D. So there we have it there's the dollar sign before the eye and the three for the standard D enter decimalizing it home tab number group decimalize to recognize fill handle double clicking to drop it down. Alright so then we're going to multiply this out this is going to be Z of one times Z of two because now we've got the two Z scores these two bits we're going to multiply them together. Let's do a format painting home tab font group painting and we painted format painter boom. And so now we can multiply these together this is going to be equal to the Z of one times the Z of two and decimalize it home tab number group decimalize fill handle double click to drop it on down. Alright so then now we've got the whole stops part but we have to sum them up. So I'm going to do that by the I'm going to make a skinny Z over here skinny Z so we can sum up the Z's to the right. This is going to be a I'm going to call it our standing for correlation. I don't think I spelled it right again correlation I thought I told I promised I was going to spell it right this time but I can't. I don't know how to spell correlation but we'll see the spell check later. I've disappointed myself I was going to look home tab font group and then we're going to say black and white. And then this is going to be the sum of Z Z of one times Z of two column. I'll put it in the outer column over here equals the sum control nine or shift nine and then control shift down picking up that entire column. Boom let's decimalize it to give us a better understanding home tab number group so we could see a little bit more about that number. And then we're going to do in minus one I'm going to put a colon because I'm going to do this little sub calculation which is going to be in space space because I don't want that other stuff less one and that's going to give me the result of in minus one without the colon. So the end is the number of rows so I'm going to say equals count I can pick up any of these in the column I'm just going to count the number of rows so I could do like this set here control shift down. Enter and there's two hundred and fifteen rows minus one I'm just going to put a one here. Let's underline it home tab font group underline subtraction I'm pulling it out into the outer column now because I have my numerator up top the denominator is going to be down below it. And this is going to be equal to this minus this there's the denominator. Let's add some decimals to it home tab number group decimalize to recognize font group. Let's put an underline underneath it as well. So that's going to give us our correlation dividing this out if I look at my formula now just to recap recap on my cap fell off and I'm trying to put it back on. So I can recap my head so it doesn't get burnt by the sun. So quick recap here. So now we have we did the top part we summed it up and then we did the denominator with this little calculation here we indicated it's a colon we pulled it into the middle. I'm going to indent as well for the indication. So let's indent this to so I'm going to go to the home tab alignment and indent. I'm going to indent this one again home tab alignment indent and then that gives us our denominator and then I'm going to divide this out this equals this divided by this. Let's decimalize it home tab numbers adding some decimals and you get a point one so pretty low number here. Let's go ahead and do the same thing now with the automatic kind of calculation here using our toolbox. So I'm going to take this data to kind of double check it and I'm going to go into the data analysis if you don't have the analysis then how do you get there you go to the file tab. You go to the options you go to the add ins you want the Excel add ins go and then toolbox I did that fast because we've done it before but if you want to be in the cool pack you need the tool pack. If you want to run with the cool pack you need the tool cat that's the statistics tool pack anyway if you want to be a cool cat you need the tool pack okay anyways and data analysis here we have the analysis. We're going to say data and we're going to say correlation we're picking the correlation okay and then the range we want this is the range. Control shift down control backspace okay and then I picked up the labels I'm going to click that we have the labels and I want to say indicate where this thing needs to go. You got to tell Excel where it goes or it's going to just throws it on some other sheet and you're like that's not where I wanted it Excel that's not where I wanted it why did you put it there. And so there we have it and you can see we get to that same correlation so it looks like we did that properly so that's nice. Let's do the same thing for that second data set that we did so we did the same thing with these two data sets to do that I'm going to need the mean and the standard deviation calculations. So maybe I'll leave this maybe I'll leave this the way it is and then I'll do that over here so I'm going to say this let's just copy this over I'll just copy this whole thing over. And say let's just copy these two columns control C and I'll put this over here didn't you plan this out before you started I had a plan. And then I'm emperor and then I decided to improvise a little bit so bear with me so we're going to do the same thing here. And then let's add another column between AI same process right click and then we're going to insert. Oh what did you do undo right click and insert not delete. And then I'm going to so let's put the mean over here so I'm going to copy these two this is going to be set one this is going to be set to. And then I'm going to say this is going to be the mean so this equals the average tab of this data set control shift down. Enter let's decimalize it home tab number decimalize to recognize this is the same thing this equals the average tab. Control shift down enter decimalize home tab number decimalize we also need the standard D standard D. And this is going to be equal to standard deviation of the sample and we'll pick up that boom. And decimalize it and then one more time the standard D because we need these for the Z you need the standard D if you want to calculate the Z. So that's what we'll do. So then let's make this a header home tab font black white and so on. All right so now I can say this is the again Z of set one and then I'm going to insert another column and this will be Z of set two. And so now we'll just take our Z scores which are going to be equal to brackets this minus the average close up the brackets. But I need to absolutize it F4 in the keyboard dollars on before the letter and number divided by the standard D. Twenty eight sixty three in this case F4 in the keyboard absolute reference enter decimalizing it home tab number decimalize double click in the fill handle to drop it down. And then we're going to do the same for this one equals brackets the five minus the standard D and F4 in the keyboard dollars on before the letter number closing up the brackets and divide. Hold on a second not minus the standard D excuse me minus the mean F4 in the keyboard closing up the brackets divided by the standard D F4 in the keyboard and close it up. Decimalize home tab number decimalize and the fill handle double click in to drop it down. All right so now let's kind of move this down a little bit because it's in the way that's in the way so and then I'm just going to kind of caught and let's add another cell multiplying this together right click and insert. And this is going to be the Z of one times the Z of two multiplying the two Z scores together equals the Z of one times the Z of two and there it is and then double click in the fill handle drop it down. Okay so now I should be able to copy this whole thing because the relative relationship should be the same. So if I just copy this whole thing and then put it like there does it come out to the answer which is correcto Mundo does this pick up all of my data it may not it does though it does. And this is taking the number and this is subtracting so I think that might be doing it in and of itself so let's double check it by now using the Excel data analysis tool. If you want to be cool you need the data analysis tool so data tab over here analysis data analysis. We want to do the correlation and we want to pick up the data this time new data set Excel new data set new day new data control shift down. And then we want to put this in another location labels are checked off Roger that and we're going to put this in a new location. Let's put it over here okay and okay so boom and so there it is the negative point 00815. So I think everything worked out the way it should just like a good movie not like the current movies they don't work out the way they should ever. That's what it that's the definition of a current movie but like a good movie and good movies that works out the way it should work out. So any case let's go ahead and just do some formatting now and I'm just going to do my blue borders here shift control shift down home tab font group and make this blue. If you don't have that blue it's in the more colors standard blue and border borderize it. Do that right here control shift down blue and borders control shift or just blue border that one I'll do it to these as well. Okay no why not blue borders blue borders so there we have it. So now it looks nice and soothing the soothing light blue makes everybody calm and peaceful yet productive and I don't know what I'm talking about but I like the blue I like the blue and then control shift down on this one. Blue borders and this one blue borders make this a header black and white black and white if you don't call it if you don't have nice colors on your worksheet. What's the point of the whole process what's the point of anything what's the point of anything if you can't have a uniform color scheme of some kind. What's going on here blue board let's move this one up a bit have it hugging right there and then blue board other thing you might want to do is have all these like skinnies the same skinny. I don't do this all the time but I'm calling I'm holding control shift letting go of control and then I'm going to hold down control for everything I think it should be like a skinny I'm holding down control. And then now I let go of control so I could scroll holding down control to click that holding down control to click that. And then holding down control to click that and then holding down control to click that and then let's make uniformly skinny skinnies and see how much that adds to that's like when you have your plate of food. And you have to have the spacers between all of the different things that are on the plate but it's better if you have them spaced out evenly which means you need a pretty large plate. So that you can properly space out the meat the potato the beans and everything without it getting all messed up so then let's do a spell check up top we can say spell check correlation still can't spell it correlation correlation correlation. Why is there an either. Oh now I ignored it. I don't want to ignore it. If that's what you say then you can do it that way but I feel like that is not should be an aid for sure anyway whatever.