Hello, and welcome to a video on how to make a correlation matrix in Excel. Today we'll be making this
correlation matrix here. A correlation matrix is a matrix of correlations, so a table
showing how two variables are correlated to one another across several different variables.
So in this correlation matrix, we have RIV-1, RIV-2, RDV, and
twoIVs. We can see that the correlation between RIV-1 and RIV-1 is
1, and it's the same for RIV-2, RIV-2, RDV, and two
IVs. I'll show you in a minute a way to prove that that's the case
through Excel formulas. We also have a correlation
between RIV-2 and RIV-1, RIV-1, and so on and so forth. And you can
see by following the horizontal and vertical lines
here, which variables are being compared to which variables. So let's go ahead and set up our correlation matrix.
We're going to use the same variables. So we're going to use R, IV-1, R,
IV-2, R-D-V,
and two IVs. It's actually
going to move that down a little,
give us some space to work. Okay, same up here.
Okay, so the formula
that we'll be using for this is =correl() short for correlation. So
CO-R-R-E-L. And when you open the parentheses on this, you see it gives us an option for
array-1 and array-2. We need two different numbers, two different arrays
of numbers for our correlation. Now in this case,
because we're in R-I-V1, R-I-V1's box, we'll be comparing R-I-V1 to R-I-V1,
which we expect to be a one. This is because they're the same variable, so if you compare something to itself,
it's going to be 100% like itself. So we'll select
all of R-I-V1, and now I'm hitting the comma button,
and I'm selecting R-I-V1 again,
and now I'll close the parentheses and hit enter. And you can see that it came out to 1. So let's just
go ahead and type 1. We don't need to do all that again to prove. We know that these will be 1.
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
below. So now, we're going to do the same stuff again. Equals, corral,
open parentheses. Except this time, we're doing it for R-I-V2, and R-I-V1. So,
we'll select all of R-I-V2,
comma, and R-I-V1 is right next to it.
Close parentheses and hit enter, and you can see we've got the same answer as above.
Okay, let's do that a few more times. A little faster this time. We'll type in equals correlation,
open parentheses, and this time we're selecting R-D-V.
Don't forget your comma, and then we're selecting R-I-V1. A correlation
matrix can be any size and can compare any amount of variables, but usually it's at least two or three,
or at least three, rather, if you just have two, then you're just doing a correlation between two variables.
Okay, now we're going to compare two IVs
to R-I-V1. All right,
and when we get to the middle of the table, we've already done R-I-V2
versus R-I-V1, so there's no reason to do that again, so now we only have to do R-I-V2
and R-D-V and R-V2 and two IVs.
So R-I-V2 and it doesn't matter which order you put
these in, I could have selected R-D-V first. Okay,
and now R-I-V2 and two IVs
be careful to
select all of your numbers when you are doing a correlation.
Okay, and our
last one will be two IVs and R-D-V, so equals
correl(), open parentheses, two IVs and
R-D-V. All right,
that's it. We have now made a correlation matrix. There's no reason to fill in the top here. It's just going
to be the same as what you had below, so this is usually left blank on a correlation matrix.
And that's it!