From the course: Learning Excel: Data Analysis

Visualize what covariance means - Microsoft Excel Tutorial

From the course: Learning Excel: Data Analysis

Start my 1-month free trial Buy for my team

Visualize what covariance means

“

- [Instructor] When you analyze data, it is often important to see how two sets of values vary in relation to one another. One way to do that is to calculate the covariance of the data sets. The covariance formula looks a little intimidating, but I'll break it down for you step by step. If you have two sets of values in columns, you can find the average of each column and the average's indicated by a bar above the variable name. So you see x bar, which is the average of all the x values, column one, and y bar is the average or mean of all of the y values. That would be your second column. Then to average the covariance of a pair of values, you subtract the mean of column one, from the first value in column one, and subtract the mean of column two, from the first value of column two, and then multiply those values together, for each pair of data points. You find the sum of all of those values and then finally, you divide by the number of data pairs. So if you have 10 pairs of values, you would divide that sum by 10, and the result is given in terms of the original data, such as dollars per mile driven, or perhaps customers versus dollar spent. The next question is how you interpret covariance values. If the value is zero, which is rare, but can happen, the data sets don't vary together at all. If you have a positive covariance, the data sets tend to move in the same direction. So if one value goes up, such as personal income, then the other value would go up, such as amount spent at your store. If the values show a negative covariance, then they tend to move in opposite directions. Finally then, you can ask, is my covariance significant? And I have to admit that this is a difficult question to answer. In general, values close to zero do indicate little relationship, and also, large positive or negative values can be significant. So look at the covariance in relation to the means of each data set. If you have a positive covariance of 500 and a mean of 100, then the covariance is five times greater than the mean, and that might be significant, but that kind of analysis does come down to individual interpretation. In practice, what most analysts do is convert covariances to correlations. But before we take that step, I will show you how to calculate covariance for different sets of values.

Contents