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
- [Instructor] When you analyze data,
it's often important to see how two sets of value
vary in relation to one another.
For example, if people drive to get to your store,
you might be interested in knowing if the distance
they drive is related to how much they spend.
In this movie, I will show you how
to look at the relationship between two sets of values
by calculating covariance.
My sample file is 05_02_SingleCovariance.
And you can find it in the Chapter05 folder
of the Exercise Files collection.
I'll show you two different ways to calculate covariance.
The first is the long way.
So I will implement the formula that you see here
in this graphic as part of the worksheet.
And then once you understand what's going on,
I will show you how to use a built-in function
to perform the same calculation.
Rather than give an overview,
I will go ahead and start implementing the formula
that you see on the right in cell C2.
So this will be for our first pair of values.
So in C2, I will type an equal sign.
And the first thing I want to do is subtract the average
of the values in column one
from the specific value in cell A2.
So I'll type a left parentheses, A2,
minus, and then we want to find the average or mean
of the values in column one.
So that's AVERAGE.
And the range is A2 to A11.
And I don't want that reference to change.
I always want to refer to that set of values
for the average.
So I'll press F4 and I'll go back to A2.
Press F4 to make that an absolute reference.
So I have my range of values,
and I can type two right parentheses
to close out that part of the calculation.
Then I'll type in asterisk for multiplication,
and we'll do the same thing for the values in column two.
So I'll go a little faster.
We're going to multiply that by, left parentheses, B2
minus the average of the values in B2.
And again, that will be an absolute reference.
Colon B11.
And F4 again on the PC.
On Mac, its command + T.
Then two right parentheses, and I will press enter
to create our calculation.
And I have a covariance of 4.68.
And it is a small value because we're working
with small values in columns A and B.
Now I want to copy this formula down.
So it covers every row of values going down
to row 11 in the worksheet.
So I will click cell C2.
Double-click the fill handle at the bottom-right corner.
I know my mouse pointer is in the right place
when it changes to a black cross.
So I double-clicked,
and there I have covariances for each pair of values.
Now, I want to add all of that up,
so I will click in cell C12.
Type an equal sign.
And then, I'll do SUM, and that will be C2 to C11.
And I want to divide that by the number of pairs,
and I'll type in a forward slash for division,
10 'cause that's the number I have, and enter.
And I have an overall covariance of 0.48.
So that is the mechanics of how the calculation is done.
Now, I'll show you how to do it with a built-in function.
So I'll click in cell E3.
Type an equal sign.
And the function that we'll use is COVARIANCE.P.
COVARIANCE.P returns the covariance of a population.
And a population calculation assumes
that you have every possible value,
and you're working with them as part of the calculation.
And then you need to enter in the two arrays of value.
So I have A2 through A11, then a comma,
and B2 through B11.
Then a right parenthesis and enter.
And you see that once again,
we get the value of negative 0.48.
So this value is very close to zero.
And even though we're working with small numbers,
that indicates that there probably
isn't a very strong relationship
between these two sets of values.
I'll go back up to E3 and double-click.
And you might have noticed that when I was entering
in the formula, we had two options
for calculating covariance.
COVARIANCE.P, which is what I demonstrated here,
and COVARIANCE.S.
S assumes that you have a sample of your data.
So I will highlight that.
Press tab, and the function has been changed
in the formula.
I'll press enter.
And you can see that the value is slightly larger,
or at least farther away from zero
than the previous calculation.
And that's because we are dividing by n minus one
instead of just n.
so it's a slightly more conservative way
of doing the same calculation.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.