In this simple guide, we are going to run through how to measure for covariance in Excel.
We will cover:
- What is covariance
- Example use case
- How to measure for covariance in Excel
- The differences between COVARIANCE.P and COVARIANCE.S
What is covariance?
Covariance is a measure within the field of statistics, and specifically probability, that allows us to measure the relationship between two sets of values.
More specifically, it determines if the trends that one set of values are taking, can be seen also occurring with a completely separate set of values, and vice versa. It doesn’t necessarily mean that one impacts the other, but that there are other variables at play (outside of the data we have) that are having a similar affect on both of these data sets.
In maths, the formula for covariance is as follows:
Examples of covariance in use
Covariance can be measured across a range of different datasets, but the most common example and (hopefully!) easiest to understand is stock prices.
We won’t go into a whole lot of detail around the techniques in analysing stocks and deciding what to potentially invest in, but put simply, it can be a good idea to have a portfolio of stocks that follow similar trends. This helps reduce risk of one crashing for reasons outside of the wider trends impacting the set that we have bought in to.
Let’s say we have two different companies X and Y, and over the course of a month they show very similar timings in terms of share price going up and down, this would likely result in what we call a positive covariance. If the opposite is happening, and one tends to go up while the other goes down, this is a negative covariance.
Generally in the context of finding additional stocks to buy within a portfolio, we would be looking to find the strongest positive covariance possible.
As a general rule, the higher the positive number returned the higher the positive covariance, and the larger the negative value the more of a negative covariance is being displayed – while zero or anything very close to it means there is little to no relationship between the two that has been found.
That’s about as technical as we are going to get for investing, so let’s move on to how to measure covariance in Excel.
Using the Excel COVARIANCE functions
Like a lot of other statistical and mathematical techniques, Excel takes out a lot of the work for us by including them as built in functions.
There are two different functions in Excel that we can use:
COVARIANCE.P – returns the covariance based on the entire population we are working with
COVARIANCE.S – returns the covariance based on a sample data set
For the rest of this guide we will be working with the COVARIANCE.P formula, but the general structure and syntax for the two are the same so everything below from a set up point of view will also work for COVARIANCE.S.
The formula syntax for covariance in Excel is below:
Let’s quicky break this down. The formula is luckily quite a simple one – we simply need to have two have two columns worth of values, and select the range of cells they both fall across.
It needs to be noted, that these are required to have the same number of values overall – if array 1 has 10 values, but array 2 only has 6 then it will result in a #N/A error.
Below we have an example of share prices over the course of 10 days for two different companies – X and Y. We also have a date column for our own reference and potential visualisation purposes, but for the covariance formula itself we simply need the two columns of actual values. Also included is the formula itself and the cells selected, along with the returned value:
As we can see, array1 sits across cells C3 to C12, while array2 is the Stock Y values of D3 to D12.
The overall result that has been returned by the formula is 1.72 – indicating there there is a positive covariance between the two companies and the price of their shares. This seems to make sense just looking at the numbers themselves as scanning down the list they do seem to have increases and decreases pretty much at the same time and of around the same percentage.
This formula comes in very handy when working with huge data sets – especially with an area such as stocks given the historical data available.
This sums up our guide on how to work with measuring covariance in Excel. As we can see, this basic formula takes that can be a bit of a lengthy mathematical calculation to do manually, and turns it into a quick and easy calculation.
For more handy guides on working with Excel, including a range of statistical techniques that can also be simplified, be sure to check out our Excel Tips page.