Covariance is another statistical tool which measures how two random variables are related to each other. Both covariance and correlation always have the same sign. When positive, the two random variables are said to be "positively" correlated, when negative, the two random variables are said to be "negatively" correlated, and when the covariance is equal to 0, the variables are said to be "uncorrelated".
We will re-use the historical closing prices of our five stocks in our covariance example.
To find the covariance matrix:
- On the XLMiner Analysis ToolPak pane, click Covariance
- Click the Input Range field and then enter the cell range B1:F6 in the spreadsheet.
- Keep columns selected for "Grouped By" since our data is arranged by column.
- Leave "Labels in First Row" selected since the first row in the data range includes the column labels.
- Click the Output Range field and then enter cell A10
- Click OK.
The results are shown below.
Like the correlation results above, the covariance matrix suggests a positive correlation between Aetna and 3M, Aetna and Abbott, and Aetna and Accenture and a negative correlation between Aetna and 3D Systems.
Note that the diagonal elements of the covariance matrix are not 1. While covariance can tell you if two variables are positively or negatively correlated, covariance can not tell you the degree of the correlation.