Check Sheet Logo
Check Sheet

COVAR

The COVAR formula calculates the covariance of two sets of data, which measures how much two variables change together. It is commonly used in statistical analysis to determine the relationship between two variables. The resulting value can be positive or negative, with a higher value indicating a stronger relationship.

Usage

Use the COVAR formula with the syntax shown below, it has 2 required parameters:

=COVAR(data_y, data_x)
Parameters:
  1. data_y (required):
    The range or array of data representing the dependent variable in the covariance calculation.
  2. data_x (required):
    The range or array of data representing the independent variable in the covariance calculation.

Examples

Here are a few example use cases that explain how to use the COVAR formula in Google Sheets.

Determining the relationship between two variables

Suppose you have two sets of data and you want to see if there is a correlation between them. Using COVAR, you can calculate the covariance and see if the two variables change together. A positive covariance indicates a positive relationship, while a negative covariance indicates a negative relationship.

Evaluating investment portfolios

Investment analysts use covariance to measure the relationship between different stocks or asset classes in a portfolio. By analyzing the covariance between different investments, analysts can determine how well the portfolio is diversified and identify any potential risks.

Predicting sales trends

Sales managers can use COVAR to analyze the relationship between different factors that affect sales. For example, they might use COVAR to see how changes in advertising spending affect sales over time.

Common Mistakes

COVAR not working? Here are some common mistakes people make when using the COVAR Google Sheets Formula:

Incorrect data range

One of the most common mistakes when using COVAR is selecting the wrong data range. Make sure that the data_y and data_x ranges are the same size and shape.

Non-numeric values in data

COVAR can only calculate the covariance between numeric values. If there are any non-numeric values in either data range, the formula will return an error. Make sure that all values in both data ranges are numeric.

Using COVAR instead of COVARP

COVAR calculates the sample covariance, whereas COVARP calculates the population covariance. Make sure you are using the correct formula for your analysis.

Incorrect order of data ranges

The order of the data_y and data_x ranges matters in COVAR. Make sure that you are using the correct order of ranges, as switching them will give you a different result.

Not accounting for missing data

If there are missing values in either data range, COVAR will return an error. Make sure to fill in any missing values or exclude them from the data ranges.

The following functions are similar to COVAR or are often used with it in a formula:

  • CORREL

    The CORREL formula returns the correlation coefficient between two sets of data. This coefficient represents the strength of the linear relationship between the two sets of data, with values ranging from -1 (perfect negative correlation) to 1 (perfect positive correlation).

  • STDEV

    The STDEV function calculates the standard deviation of a set of numbers. It measures the amount of variation or dispersion of a set of values from the average (mean) value. It is commonly used in statistics to determine the spread of a data set. The values can be supplied as individual cells, ranges, or constants.

  • AVERAGE

    The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

Learn More

You can learn more about the COVAR Google Sheets function on Google Support.