1 00:00:00,000 --> 00:00:02,766 2 00:00:02,766 --> 00:00:08,799 Hello, and welcome to a video on how to make a correlation matrix in Excel. Today we'll be making this 3 00:00:08,800 --> 00:00:14,800 correlation matrix here. A correlation matrix is a matrix of correlations, so a table 4 00:00:14,800 --> 00:00:14,833 5 00:00:14,833 --> 00:00:21,399 showing how two variables are correlated to one another across several different variables. 6 00:00:21,400 --> 00:00:22,566 7 00:00:22,566 --> 00:00:28,799 So in this correlation matrix, we have RIV-1, RIV-2, RDV, and 8 00:00:28,800 --> 00:00:34,833 twoIVs. We can see that the correlation between RIV-1 and RIV-1 is 9 00:00:34,833 --> 00:00:34,866 10 00:00:34,866 --> 00:00:41,199 1, and it's the same for RIV-2, RIV-2, RDV, and two 11 00:00:41,200 --> 00:00:47,300 IVs. I'll show you in a minute a way to prove that that's the case 12 00:00:47,300 --> 00:00:53,600 through Excel formulas. We also have a correlation 13 00:00:53,600 --> 00:00:53,633 14 00:00:53,633 --> 00:00:59,833 between RIV-2 and RIV-1, RIV-1, and so on and so forth. And you can 15 00:00:59,833 --> 00:00:59,866 16 00:00:59,866 --> 00:01:06,266 see by following the horizontal and vertical lines 17 00:01:06,266 --> 00:01:06,299 18 00:01:06,300 --> 00:01:12,400 here, which variables are being compared to which variables. So let's go ahead and set up our correlation matrix. 19 00:01:12,400 --> 00:01:12,900 20 00:01:12,900 --> 00:01:19,633 We're going to use the same variables. So we're going to use R, IV-1, R, 21 00:01:19,633 --> 00:01:19,999 22 00:01:20,000 --> 00:01:24,366 IV-2, R-D-V, 23 00:01:24,366 --> 00:01:26,332 24 00:01:26,333 --> 00:01:28,733 and two IVs. It's actually 25 00:01:28,733 --> 00:01:32,366 26 00:01:32,366 --> 00:01:37,599 going to move that down a little, 27 00:01:37,600 --> 00:01:38,600 28 00:01:38,600 --> 00:01:44,400 give us some space to work. Okay, same up here. 29 00:01:44,400 --> 00:01:48,333 30 00:01:48,333 --> 00:01:52,899 Okay, so the formula 31 00:01:52,900 --> 00:01:54,733 32 00:01:54,733 --> 00:02:00,833 that we'll be using for this is =correl() short for correlation. So 33 00:02:00,833 --> 00:02:00,866 34 00:02:00,866 --> 00:02:06,999 CO-R-R-E-L. And when you open the parentheses on this, you see it gives us an option for 35 00:02:07,000 --> 00:02:07,033 36 00:02:07,033 --> 00:02:13,066 array-1 and array-2. We need two different numbers, two different arrays 37 00:02:13,066 --> 00:02:19,132 of numbers for our correlation. Now in this case, 38 00:02:19,133 --> 00:02:25,033 because we're in R-I-V1, R-I-V1's box, we'll be comparing R-I-V1 to R-I-V1, 39 00:02:25,033 --> 00:02:25,499 40 00:02:25,500 --> 00:02:31,866 which we expect to be a one. This is because they're the same variable, so if you compare something to itself, 41 00:02:31,866 --> 00:02:38,066 it's going to be 100% like itself. So we'll select 42 00:02:38,066 --> 00:02:38,099 43 00:02:38,100 --> 00:02:44,400 all of R-I-V1, and now I'm hitting the comma button, 44 00:02:44,400 --> 00:02:45,500 45 00:02:45,500 --> 00:02:47,333 and I'm selecting R-I-V1 again, 46 00:02:47,333 --> 00:02:55,033 47 00:02:55,033 --> 00:03:01,066 and now I'll close the parentheses and hit enter. And you can see that it came out to 1. So let's just 48 00:03:01,066 --> 00:03:01,099 49 00:03:01,100 --> 00:03:06,666 go ahead and type 1. We don't need to do all that again to prove. We know that these will be 1. 50 00:03:06,666 --> 00:03:07,466 51 00:03:07,466 --> 00:03:13,966 And in your assignment, you don't have to do this equals correlation in the first one either. You only have to do it for your actual correlations 52 00:03:13,966 --> 00:03:19,832 below. So now, we're going to do the same stuff again. Equals, corral, 53 00:03:19,833 --> 00:03:20,266 54 00:03:20,266 --> 00:03:26,299 open parentheses. Except this time, we're doing it for R-I-V2, and R-I-V1. So, 55 00:03:26,300 --> 00:03:28,366 we'll select all of R-I-V2, 56 00:03:28,366 --> 00:03:33,266 57 00:03:33,266 --> 00:03:37,299 comma, and R-I-V1 is right next to it. 58 00:03:37,300 --> 00:03:47,366 59 00:03:47,366 --> 00:03:52,199 Close parentheses and hit enter, and you can see we've got the same answer as above. 60 00:03:52,200 --> 00:03:54,000 61 00:03:54,000 --> 00:04:00,233 Okay, let's do that a few more times. A little faster this time. We'll type in equals correlation, 62 00:04:00,233 --> 00:04:00,533 63 00:04:00,533 --> 00:04:05,399 open parentheses, and this time we're selecting R-D-V. 64 00:04:05,400 --> 00:04:10,166 65 00:04:10,166 --> 00:04:16,432 Don't forget your comma, and then we're selecting R-I-V1. A correlation 66 00:04:16,433 --> 00:04:19,299 67 00:04:19,300 --> 00:04:25,733 matrix can be any size and can compare any amount of variables, but usually it's at least two or three, 68 00:04:25,733 --> 00:04:28,133 69 00:04:28,133 --> 00:04:34,266 or at least three, rather, if you just have two, then you're just doing a correlation between two variables. 70 00:04:34,266 --> 00:04:35,666 71 00:04:35,666 --> 00:04:40,066 Okay, now we're going to compare two IVs 72 00:04:40,066 --> 00:04:42,632 73 00:04:42,633 --> 00:04:45,066 to R-I-V1. All right, 74 00:04:45,066 --> 00:04:54,166 75 00:04:54,166 --> 00:04:59,732 and when we get to the middle of the table, we've already done R-I-V2 76 00:04:59,733 --> 00:05:01,533 77 00:05:01,533 --> 00:05:07,866 versus R-I-V1, so there's no reason to do that again, so now we only have to do R-I-V2 78 00:05:07,866 --> 00:05:08,866 79 00:05:08,866 --> 00:05:11,999 and R-D-V and R-V2 and two IVs. 80 00:05:12,000 --> 00:05:17,966 81 00:05:17,966 --> 00:05:24,066 So R-I-V2 and it doesn't matter which order you put 82 00:05:24,066 --> 00:05:24,099 83 00:05:24,100 --> 00:05:27,500 these in, I could have selected R-D-V first. Okay, 84 00:05:27,500 --> 00:05:35,100 85 00:05:35,100 --> 00:05:41,233 and now R-I-V2 and two IVs 86 00:05:41,233 --> 00:05:41,699 87 00:05:41,700 --> 00:05:47,500 be careful to 88 00:05:47,500 --> 00:05:50,900 89 00:05:50,900 --> 00:05:57,033 select all of your numbers when you are doing a correlation. 90 00:05:57,033 --> 00:05:57,066 91 00:05:57,066 --> 00:06:02,932 Okay, and our 92 00:06:02,933 --> 00:06:03,266 93 00:06:03,266 --> 00:06:09,299 last one will be two IVs and R-D-V, so equals 94 00:06:09,300 --> 00:06:09,633 95 00:06:09,633 --> 00:06:14,533 correl(), open parentheses, two IVs and 96 00:06:14,533 --> 00:06:18,433 97 00:06:18,433 --> 00:06:22,499 R-D-V. All right, 98 00:06:22,500 --> 00:06:24,833 99 00:06:24,833 --> 00:06:31,033 that's it. We have now made a correlation matrix. There's no reason to fill in the top here. It's just going 100 00:06:31,033 --> 00:06:31,066 101 00:06:31,066 --> 00:06:36,866 to be the same as what you had below, so this is usually left blank on a correlation matrix. 102 00:06:36,866 --> 00:06:37,499 103 00:06:37,500 --> 00:06:38,533 And that's it!