All language subtitles for 03 - Calculate covariance among multiple pairs of columns

- [Instructor] In the previous movie,

I showed you how to calculate covariance

for two columns of data.

In this movie, I will show you do it

for multiple columns of data by creating a grid.

My sample file is 05_03_MultipleCovariance

and you can find it in the Chapter05 folder

of the Exercise Files collection.

I have four columns of data in this workbook

and you can see them over here in columns A through D.

And then I also have the covariance formula

over here on the right as a reminder

but we're actually not going to implement it directly.

Instead, we're going to use a built-in function.

It'll save us a lot of time.

To start, I will click in cell G2.

And I want to calculate the covariance

of column one with itself.

And no, this calculation doesn't make any sense

in any real way but what it will let us do

is create formulas that we can copy

to the rest of the cells in the worksheet.

So in cell G2,

I will type an equal sign

and I will use COVARIANCE.S

and this assumes that we have a sample

instead of every possible value for our dataset.

The first array is A2 to A11.

And I want to make this reference unchanging.

So I will click inside of A2

and press F4.

That would be Command + T on the Mac

to make it an absolute reference

and I'll do the same thing for A11.

F4.

Then type a comma

and A2 through A11 again.

So I'll just select those cells.

Now I'll type a right parentheses and Enter,

and we get a value that actually doesn't mean anything.

But that's fine because we have a base to work from now.

I want to copy the formula

that I just created down to cell G3

so we can compare column one and column two.

So I will double click cell G2.

Select the text of the formula.

Press Control C to copy it, then I'll press Escape.

And double click G3.

Press Control + V to paste

and now I want to base my calculations not on A2 to A11

but B2 to B11, comparing that to the values in column A.

So I'll type B2.

And then change it to B11

so I can see my two separate columns.

Press Enter and I have the covariance

of values between those two columns.

Now I can do the same thing for column three

and column four.

I'll move a little bit more quickly.

So I will double click in cell G4 to open it for editing.

Press Control + V, given my original formula

that I copied was still available.

And I will change the first references

from A2 to A11 to C2 to C11 and Enter.

Have covariance there.

I'll do the same thing in cell G5.

And I'll make it D2 to D11

for the first array.

Press Enter and there I have the covariance

between column one and column four.

Now with the formulas I just created,

I am able to copy them over

to the remaining cells in rows H, I and J

to calculate the covariance for every combination

of columns in my dataset.

So I'll select cells G2 through G5.

Grab the fill handle at the bottom right corner

of cell G5.

And I know my mouse pointer's in the right place

when it turns into a black cross.

So I will drag over.

And there I have all of my calculations.

So if I want to see the covariance

between column two and column three,

I can go to either cell that indicates that value.

So I have in cell H4 for column two and column three, -8.17

and if I go to the intersection

of column two and column three.

I see the same value.

So the values are repeated

but creating the formulas the way that I showed you

actually allows you to accelerate the process

with a little bit of repetition of result.