In this video, learn how to calculate covariance between two sets of data.
- [Instructor] In the previous movie I described covariance and how you can use it to analyze two data sets, and how they relate to one another. In this movie, I will show you how to calculate covariance for two separate data sets. My sample file is single covariance, and you can find it in the chapter five folder of your exercise files collection. What I'm doing is calculating, the covariance between two sets of data. And you see those in columns A and B. Labeled column one and column two.
Covariance is the average of all of the differences or variances for pairs of data points. You can see the formula to the right of the covariance function label. I will show you how to perform the calculation the long way, and then I will show you how to do it using a function. In cell C two, which I already have selected, I need to implement the formula that appears to the right of the covariance function label. So, I'll type in equal sign, I want to subtract the average of all of my x values, that's column one from the average.
So, the first thing I'll do is type a left parenthesis, then I'll type a2, which is the first x value. And from that I will subtract the average of all values in column A. And that is the average of the range a2, and I don't want this reference to change, So I'll press F four to a11, and then F four to make the reference absolute or unchanging, then a right parentheses and another right parentheses.
Now, I want to multiply this by the same thing for the column two or y values. So I will type an asterisk for multiplication, then left parentheses and then b2, and again, I want that to change minus the average of the values from b2 to b11. So b2, F four to make it an absolute, or unchanging reference colon b11, F four again, right parentheses to close out the average function, and then right parentheses again to close out the second half of the formula, Press enter, and I have a covariance of 4.68 for that data pair.
Now I can copy my formula down for all of my other data pairs. So I will click cell C two and then move my mouse pointer over the Phil handle, the green square at the bottom right corner. when my mouse pointer changes to a black cross here I'll double click, and the values are copied down. Now click at cell C12, and find the average of all those covariances. And that is exactly the same as dividing by the number of data pairs. So I'll type equal average C two through C11, right parentheses and enter.
And I get a covariance of 0.48, which is very small. I wanted you to see how to calculate covariance the long Way before I showed you how to use the functions that are available. I'll click the cell E three type an equal sign, and let's assume that you know that you have all possible data values. For that you'd use the function covariance.p that is the first one that pops up after you type COV. So type right parentheses and array one is a2 to a11, then a comma and array two is b2 to b11.
Right parentheses and enter and you get the same value of minus point four eight. You might have noticed that there was a second covariance function and that was covariance.s. The difference between covariance.s and covariance.P is that covariance.s assumes you only have a sample of all values not the entire population. To make the change you would go to your cell, I'll double click cell cell E three, and I will edit from covariance.p and covariance.s and enter, and you see I get a difference covariant function value.
The difference is due to subtracting one from the number of data pairs.
- Distinguish between the mean, median, and mode.
- Describe the relationship between variance and standard deviation.
- Identify a nondirectional hypothesis.
- Point out the difference between COVARIANCE.P and COVARIANCE.S.
- Explain correlation.
- Analyze Bayes’ rule.