When you analyze business data, it's likely that you may have more than one set of data. In this video, learn how to create a covariance table so that you can easily calculate covariance between multiple sets of data.
- [Instructor] When you capture data about your business it's likely that you will capture several type of values. In this movie, I will show you how to calculate covariance among several columns of data at the same time. I'll work with four columns but the techniques that I show will work for any number. My sample file is MultipleCovariance and you can find it in the chapter five folder of the exercise files collection. Covariance is a measure of how two sets of variables relate to each other, in other words, whether they tend to move in same direction, opposite directions, or if they're not related at all.
Well, a covariance calculation only looks at two sets of values. You can create a worksheet that calculates the relationships between multiple pairs of columns. I've set up my worksheet so that in columns A through D, I have values for four different sets of data, and then in cells F1 through J5, I have column headings, and what I'll do is create a lookup table. For example, in cell G2, I will have the covariance of Column1 as a compared to Column1.
And cell G3, I will compare Column2 and Column1 and so on. Let's start by creating the covariance calculation for Column1 with itself. And no, this is not a meaningful comparison because it's the same data, but creating the formula here allows us to copy the formula to other cells in our table. So I'll start in cell G2 by typing an equal sign, and then I will create my covariance function. I'll use COVARIANCE.S. It's a more conservative measure of covariance because its only looking at the sample and not assuming we have the entire population of data.
So I'll highlight COVARIANCE.S, then a left parentheses, and now I need to indicate the two arrays of values that I want to compare. So I'll start will cell A2 and go down to cell A11. I don't want this reference to change when I copy the formula over, so I will click between the A and the two and press F4 to make an absolute reference. And I'll do the same thing for A11.
So I press F4, then I'll type a comma. And the second array will be able to change, and that will be A2 colon A11. Again, we're comparing it to itself, but that will allow us to copy effectively. I'll press Enter, and we have a covariance of 8.26667 and so on. I can use the formula that I just created to calculate the covariance for Column1 and Column2.
So I will double click cell G2 and then on the formula bar, I will select the formula text and then press Control + C to copy it. I'll press Escape to stop editing cell G2, and I'll double click cell G3. Then I'll press Control + V to paste in the previous formula. Now I want to compare Column2 to Column1, and Column2 is in worksheet column B.
So I will change the references from A2 to B2 and A11 to B11. So this is my first array. I'm comparing Column2 to Column1. Press Enter, and I get a slightly negative value. Now I could do the same thing in Column3, and this time I'll be changing the references for the first array to column C of my data. So I'll double click in cell G4, press Control + V, and instead of A2 to A11, I'll do C2 to C11, and Enter.
And I'll do the same thing for Column4. Double click in G5, Control + V, and we're looking at D2 to D11 as compared to Column1 in A2 to A11. Enter, and there we see the covariance between Column1 and Column4. Now because of the way that I have my formulas set up, I can copy the formulas from G2 to G5 over to J2 to J5. So I will select cells G2 to G5 and then drag the fill handle.
It's at the bottom right corner of the selection. I know my mouse pointer's in place when it changes to a black crosshair. And I'll copy over, and you see the values. One way to make sure that the formulas were copied correctly is to check the corresponding values. So for example, if we are looking at Column1 versus Column4, then in cell G5, we have the value 4.2 repeating and we have the same value in J2, which also compares Column1 and Column4.
So you can look up the covariance between any pairs of columns in this table.
- 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.