COVARIANCE.S function

The COVARIANCE.S function in Excel calculates the sample covariance between two data sets. Unlike COVARIANCE.P, which calculates the population covariance, COVARIANCE.S is used when your data represents a sample (a subset of a population), and you want to estimate the covariance of the entire population based on that sample.

Covariance measures how two variables change together:

• Positive covariance: As one variable increases, the other tends to increase.
• Negative covariance: As one variable increases, the other tends to decrease.
• Zero covariance: No linear relationship between the variables.

Syntax:

COVARIANCE.S(array1, array2)

Arguments:

• array1: The first range of data values (representing the first variable).
• array2: The second range of data values (representing the second variable).

Both arrays must have the same number of data points, and the corresponding values should represent paired observations.

Example:

Suppose you have two data sets:

• array1 (in range A1:A5): 10, 20, 30, 40, 50
• array2 (in range B1:B5): 15, 25, 35, 45, 55

To calculate the sample covariance between these two sets, you would use the formula:

=COVARIANCE.S(A1:A5, B1:B5)

This will return the sample covariance, which is an estimate of the covariance of the two variables in the broader population.

Key Points:

• Sample covariance is used when your data represents a sample from a larger population. It adjusts for the fact that you are working with a sample instead of the entire population.
• The formula for sample covariance divides by n – 1 (where n is the number of data points), whereas COVARIANCE.P divides by n for the population.
• Covariance values can be positive, negative, or zero, indicating the relationship between the two variables.
• If both variables move in the same direction, the covariance is positive; if they move in opposite directions, the covariance is negative.

Use Cases:

• Evaluating the relationship between two sample datasets, such as measuring the relationship between the returns of two stocks based on sample data.
• Estimating how changes in one variable may affect changes in another variable, using a smaller data sample rather than the entire population.