Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
(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.