All language subtitles for 08 - Solution Calculate correlations between columns of data

(upbeat music)

- [Instructor] I hope you've had a chance to work

with the problem presented in this workbook.

I'd like to now show you how I would solve it.

And again, my sample file is Chapter_5_Challenge

and you can find it in the chapter 5 folder

of the exercise files collection.

The first task is to go to cell F 2

and calculate the co-variance

between the values and the customers and sales columns.

So in F 2 I'll type in equal sign

and I will start typing covar

and because you can never be sure

that you have all of your data,

I'm going to use COVARIANCE.S

which assumes that we have a sample,

so I'll highlight that, press tab,

and now I need to identify the two arrays.

The first one is customers

and those values are in A 2 through A 12

then a comma and then sales, which are in column B.

So B 2 to B 12 I've selected there, right parenthesis

and enter, and we get a covariance of 4023.16.

How you interpret that covariance value

depends on your original data.

In this case, it does appear just from visual inspection

that the number of customers

in sales tend to co-vary together

and 4,023 is about twice the maximum value

in the sales column, so that's a good indicator

that there probably is a relationship.

Now let's do a correlation

for the same two data sets and see what that does.

So I'll go to cell F 4, type in equal sign,

and we'll have CORREL, C O R R E L,

and again, array1 and array2,

array1 is A 2 through A 12, then a comma,

and then array2, B 2 to B 12, right parenthesis, and enter.

And I get a correlation of customers and sales of 0.84

and that is very high.

Remember that correlation varies between negative one

and one with a value of one

meaning that the values are perfectly correlated.

And in a case where you have a value of 0.84

with only 11 data points, that's a very good indication

that there is a correlation between those two sets of data.

Finally, let's go ahead

and create a grid that calculates the correlation

for each pair of columns.

So I'll start by clicking and cell G 8

and I want to create a formula that will calculate

the correlation of the customer's column,

column A with itself and I'll be able to copy that formula

throughout the rest of the grid

and find correlations between every pair of columns.

So in G 8 I'll type = and then correl, C O R R E L,

left parentheses and array1 is A 2 through A 12.

However, with the way I'm creating my worksheet,

I don't want that reference to change

when I copy the formula.

So I'll press F4

and that makes the entire reference absolute and unchanging,

type a comma and then I'll select A 2

through A 12 again, right parenthesis and enter,

and I get a correlation of one

which as I mentioned earlier is correct.

I'm comparing customers to itself.

Now I will double click cell G 8 and copy the formula,

so remember I have copied the cell's formula text.

I have not actually copied the cell.

So it is important

that you not just click a cell and press control C.

So I've already copied the values

and then press escape to stop editing the cell,

double click cell G 9, press control V to paste the text,

and now, because I want to change the absolute references

from A 2 to A 12, to B 2 to B 12

I will edit the first range reference so I have B 2 to B 12

and because I'm comparing customers sales

I will leave A 2 to A 12

as the relative references unchanged, press enter.

And I get a correlation of 0.842

which we can see is correct because it matches

the calculation we did earlier in cell F 4.

Now I will double click cell G 10,

press control V, there's my formula again

and I will do the same thing for the first array

except this time I'm changing it to column C returns.

So C 2 to C 12, comparing it to A 2 to A 12, press enter,

and I see a very slightly negative correlation

but that is very close to zero.

Now I can copy the results

of these three formulas over to cover columns H and I,

so I will select G 8 through G 10

and drag the fill handle

to the right until it covers the same rows in column I,

and there I get the values

and it looks like everything is correct

because I have customers and customers at one,

sales and sales at one, and returns and returns at one.

And I can see also that sales and returns do not

appear to be correlated,

a correlation value of 0.126 let's call it, is very small.

So it appears that the only meaningful relationship

at least from a correlation standpoint

that I have is between sales and customers per day.