COVARIANCE.S: Excel Formulae Explained

The COVARIANCE.S function in Excel is a statistical formula that calculates the sample covariance between two sets of values. Covariance is a measure that indicates the amount to which two random variables change together. In this context, the COVARIANCE.S function can be used to determine how much two ranges of data move together. This function is particularly useful in finance and economics, where it is often used to calculate the covariance of stock returns.

Understanding Covariance

Before diving into the specifics of the COVARIANCE.S function, it's important to understand what covariance is. Covariance is a statistical concept that measures the relationship between the movements of two variables. When the covariance is positive, the variables tend to move in the same direction. Conversely, when the covariance is negative, the variables tend to move in opposite directions.

However, covariance does not measure the degree of dependency between two variables. It only indicates the direction of the relationship. For instance, a high positive covariance between two variables does not necessarily mean that one variable increases or decreases in direct proportion to the other.

COVARIANCE.S Function Syntax

The COVARIANCE.S function in Excel has a simple syntax. It requires two arguments, both of which must be arrays or ranges of numerical data. The syntax is as follows:

COVARIANCE.S(array1, array2)

Where array1 and array2 are the two arrays or ranges of data for which you want to calculate the covariance. It's important to note that the two arrays must have the same number of data points.

How to Use COVARIANCE.S Function in Excel

Step-by-Step Guide

Using the COVARIANCE.S function in Excel is straightforward. Here's a step-by-step guide:

  1. Open Excel and enter your data in two separate columns or rows.
  2. Select an empty cell where you want the covariance to be calculated.
  3. Type =COVARIANCE.S( into the cell.
  4. Select the range of cells for your first array, followed by a comma.
  5. Select the range of cells for your second array.
  6. Close the parentheses and press Enter.

Excel will then calculate the covariance between the two sets of data and display the result in the selected cell.

Example

Let's consider an example where we have two sets of data representing the monthly returns of two stocks over a year. We want to calculate the covariance of these returns to understand how the stocks move together.

The data is as follows:

Stock A ReturnsStock B Returns1%2%3%4%

To calculate the covariance, we would use the COVARIANCE.S function as follows:

=COVARIANCE.S(A2:A13, B2:B13)

Where A2:A13 and B2:B13 are the ranges of cells containing the returns of Stock A and Stock B, respectively.

Interpreting the Results

Once you've calculated the covariance using the COVARIANCE.S function, interpreting the results is the next step. As mentioned earlier, a positive covariance indicates that the variables tend to move in the same direction, while a negative covariance indicates that they tend to move in opposite directions.

However, the magnitude of the covariance doesn't have a straightforward interpretation. This is because the magnitude of the covariance depends on the units of the variables. Therefore, it's often more useful to calculate the correlation coefficient, which is a normalized measure of the dependence between two variables.

Limitations of the COVARIANCE.S Function

While the COVARIANCE.S function is a powerful tool for understanding the relationship between two variables, it has some limitations. First, it can only calculate the covariance between two variables. If you want to understand the relationship among more than two variables, you would need to use other statistical techniques, such as multivariate analysis.

Second, the COVARIANCE.S function can only handle numerical data. If your data includes non-numerical values, such as text or boolean values, you would need to convert these values into numerical form before using the COVARIANCE.S function.

Conclusion

The COVARIANCE.S function in Excel is a useful tool for understanding the relationship between two sets of data. By calculating the covariance, you can gain insights into how two variables move together. However, it's important to remember that covariance is just one measure of the relationship between variables, and it should be used in conjunction with other statistical measures for a more complete understanding of the data.

Take Your Data Analysis Further with Causal

Now that you understand how to use the COVARIANCE.S function in Excel to analyze the relationship between data sets, imagine taking your data analysis to the next level. With Causal, you can perform complex calculations, create dynamic visualizations, and present your findings in interactive dashboards with ease. If you're ready to enhance your modelling, forecasting, and scenario planning capabilities beyond traditional spreadsheets, sign up today for free and start exploring the intuitive world of Causal.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.