COVAR: Excel Formulae Explained

The COVAR function in Excel is a statistical function that calculates the covariance, the average of the products of deviations for each data point pair in two data sets. This function is often used in finance and statistical projections to determine the relationship between two sets of data. The covariance is used to determine how much two random variables vary together, and it is the basis for many other statistical functions.

Understanding the COVAR Function

The COVAR function is categorized under Excel Statistical functions. It will calculate the covariance of two given sets of values. A positive covariance indicates that the two sets of values tend to move together—that is, when one set goes up, the other set also tends to go up. A negative covariance indicates the opposite. If the covariance is zero, it indicates that there is no linear relationship between the two sets of values.

The COVAR function was replaced with the COVARIANCE.P and COVARIANCE.S functions in Excel 2010 to improve accuracy. However, the function is still available for compatibility with earlier versions of Excel.

COVAR Syntax

The syntax for the COVAR function in Microsoft Excel is:

COVAR( array1, array2 )

Where 'array1' is the first set of data points and 'array2' is the second set of data points. Both array1 and array2 must be of the same size and contain numbers, else the function will return a #N/A error.

How to Use the COVAR Function

Step-by-Step Guide

Here is a step-by-step guide on how to use the COVAR function in Excel:

  1. Open Excel and select the cell where you want the result to be displayed.
  2. Type =COVAR( into the selected cell.
  3. Select or type in the range of cells for your first array.
  4. Type a comma , to separate the two arrays.
  5. Select or type in the range of cells for your second array.
  6. Close the function with a parenthesis ) and hit Enter.

The result displayed is the covariance of the two sets of data points.

Example

Let's consider an example to understand how the COVAR function works:

Suppose we have two sets of data points:

  • Array1: 3, 2, 4, 5, 6
  • Array2: 9, 7, 12, 15, 17

Here's how you can use the COVAR function:

=COVAR(A2:A6, B2:B6)

The function returns 8.5, which is the covariance of the two sets of data points.

Common Errors with the COVAR Function

While using the COVAR function, you may encounter the following errors:

#N/A Error

This error occurs when the two arrays of data points are not of the same size. Both arrays must contain the same number of data points.

#VALUE! Error

This error occurs when one or both of the arrays contain non-numeric data points. The COVAR function only works with numbers.

Conclusion

The COVAR function in Excel is a powerful tool for statistical analysis and financial modeling. It allows you to determine the relationship between two sets of data points, providing valuable insights into trends and patterns. While the function has been replaced by more accurate functions in recent versions of Excel, it remains a useful tool for compatibility with earlier versions.

Understanding how to use the COVAR function can enhance your Excel skills and enable you to perform more complex data analysis tasks. With practice, you can use this function to make more informed decisions based on your data.

Take Your Data Analysis Further with Causal

If you're ready to elevate your data analysis beyond traditional spreadsheet limitations, Causal is your next step. Designed specifically for number crunching and data visualization, Causal simplifies modelling, forecasting, and scenario planning. You can effortlessly create interactive dashboards to present your findings with clarity. Why not experience the difference for yourself? Sign up today for free and discover a more intuitive way to work with your data.

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.