All language subtitles for 06 - Calculate correlation among multiple pairs of columns

- [Instructor] In the previous movie, I showed you how

to create a formula to calculate correlation

between two sets of data.

In this movie, I'd like to show you a quick way to set

up a grid so you can analyze the correlation

between multiple columns of data.

My sample file is 05 06 Multiple Correlation,

and you can find it in the chapter five folder

of the exercise files collection.

And as you can see on the left, starting in column A,

I have four sets of data.

Each of them contains 10 rows or 10 values.

And then over on the right, starting with labels

in column F, I have a grid setup so

that we can examine the correlation

between pairs of columns.

You are always performing your correlation analysis

between one set of values and another.

So, let's start by creating a formula that we can copy

to other cells and will let us set up the grid efficiently.

I'll start in cell G6.

So, I'll type an equal sign,

and I want to find the correlation.

So, that's the C-O-R-R-E-L, or correl, function,

and I'll press Tab.

And our first array will be in column A,

so I'll select A3 through A12,

and I want to make these references absolute or unchanging.

So, I'll click in the middle of A3, press F4,

that's on Windows.

On the Mac, you would press Command + T,

and I'll do the same thing for A12.

So, I'll press F4, and then I'll click to the right

of a cell reference, type a comma,

and now I want to repeat A3 to A12,

but make it a relative, or changing, reference.

So, A3 colon A12 right parenthesis, and enter,

and I get a correlation of one,

and that is exactly what I want.

I am correlating a column with itself.

So, of course, the values move exactly as one would expect.

So, the correlation is one.

If you get a different value, you did something wrong.

Now, I want to create another version of that formula

for column B.

So in G7, I'll type an equal sign, correlation,

and it'll be B3 to B12.

And again, I want to make the references absolute.

F4, not four.

There we go.

Then a comma and A3

through A12 because we're comparing column two

to column one.

So, A3 to A12, and again, we want to make this changing.

Right parenthesis, and enter,

and we have a small negative correlation.

Same in G8, so, equal correlation C3 to C12,

make those references absolute.

Then comma and A3 to A12, and enter.

And last one, equal correlation, I'm working in cell G9.

That will be D3 absolute to D12 absolute, pressing F4,

comma A3 to A12, and enter.

Great.

So, I have my formulas, and now I can copy them over

to column two, three, and four, H, I, and J

of the worksheet so that I can calculate correlations

between each pair of columns.

So, I'll select cells, G6 through G9,

and drag the fill handle over, and the first thing I note,

and this is a good sign, is that anytime I compare a column

with itself, I get a correlation of one.

So, between column one and column one,

column two and column two, three and three, four and four,

I get correlations of one.

So, along the main diagonal of this grid

I see that I have the correlations that I'm looking for.

Another way that I can check my values is to ensure

that column one and column two,

when column one is a row header

and column two is a column header,

I need to make sure

that produces the same result as when I have column two

as the row header and column one as the column header.

So, it looks like my values, even though they're duplicated

within this grid, they are accurate.

So, if I want to compare column two to column three,

I can do it here, or I can do it here,

and I will get a correct result based on my calculations.