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

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

(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.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.