# COVARIANCE.S Function in Excel

Part 1: Introduction

• Definition The COVARIANCE.S function in Excel is used to find the covariance, which is the average of the product of deviations for each data point pair in two data sets.

• Purpose: This function aims to determine how much two data sets move together. If the covariance is positive, the two sets will likely increase. If the covariance is negative, one group will likely increase as the other decreases.

• Syntax & Arguments
`=COVARIANCE.S(array1, array2)`

The function takes in two arguments:

1. array1: This is the first range of cells or an array of numbers.
2. array2: This is the second range of cells or various numbers.
• Return value The function returns the sample covariance, a measure of how much two sets of data move together.

• Remarks Both arrays must have the same number of data points. Also, the function ignores text and logical values that you supply as part of the arrays.

Part 2: Examples

Example 1

• Purpose of Example: To determine the covariance between sales data and advertising spend.
ABCD
2503=COVARIANCE.S(A2:A4,B2:B4)10
3603.5
4704
• Explanation: In this example, the covariance value will tell us whether increased advertising spend leads to increased sales. In this case, the covariance of 10 suggests that increased advertising spend is associated with increased sales.

Example 2

• Purpose of Example: To calculate the covariance between customer traffic and sales numbers at a retail store.
ABCD
1Customer Traffic (100s)Sales (1000s)FormulaResult
21050=COVARIANCE.S(A2:A4,B2:B4)-2
31540
42030
• Explanation: In this example, the negative covariance of -2 suggests that as customer traffic increases, sales decrease, which is quite unusual and indicates that other factors may be influencing the outcome of the sales.

Example 3

• Purpose of Example: To calculate the covariance between employee experience (in years) and their salary (in 1000s).
ABCD
1Experience (Years)Salary (1000s)FormulaResult
2135=COVARIANCE.S(A2:A4,B2:B4)25
3345
4560
• Explanation: In this example, the positive covariance value of 25 suggests that as an employee’s years of experience increase, their salary also tends to increase.

Example 4

• Purpose of Example: Calculate the covariance between production costs and product sales price.
ABCD
1Production Cost (1000s)Sales Price (1000s)FormulaResult
2220=COVARIANCE.S(A2:A4,B2:B4)15
3330
4440
• Explanation: In this example, the positive covariance value of 15 suggests that the sales price also tends to increase as production costs rise.

Example 5

• Purpose of Example: To calculate the covariance between daily hours of operation and total sales.
ABCD
1Hours of OperationTotal Sales (1000s)FormulaResult
2835=COVARIANCE.S(A2:A4,B2:B4)-1
31040
41245
• Explanation: Here, the negative covariance value of -1 suggests that as the hours of operation increase, the total sales tend to decrease, which is not a typical situation and could offer inefficiencies in operations during longer hours.

Remember, the COVARIANCE.S function is handy when we want to understand if there’s a relationship between two sets of data. Just bear in mind that it doesn’t tell us about the strength of the relationship. Use it wisely, and remember to interpret the results in the context of your specific situation.

Part 3: Tips and tricks

1. Ensure both data sets have the same number of data points.
2. Use covariance to determine if there’s a relationship between two sets of data. Positive covariance indicates that the two data sets are likely to increase together.
3. Be aware that the covariance value doesn’t indicate the strength of the relationship between the two data sets. A more considerable absolute value does not necessarily mean a stronger relationship.