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:
- array1: This is the first range of cells or an array of numbers.
- 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.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sales (1000s) | Advertising Spend (1000s) | Formula | Result |
| 2 | 50 | 3 | =COVARIANCE.S(A2:A4,B2:B4) | 10 |
| 3 | 60 | 3.5 | ||
| 4 | 70 | 4 |
- 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.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer Traffic (100s) | Sales (1000s) | Formula | Result |
| 2 | 10 | 50 | =COVARIANCE.S(A2:A4,B2:B4) | -2 |
| 3 | 15 | 40 | ||
| 4 | 20 | 30 |
- 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).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Experience (Years) | Salary (1000s) | Formula | Result |
| 2 | 1 | 35 | =COVARIANCE.S(A2:A4,B2:B4) | 25 |
| 3 | 3 | 45 | ||
| 4 | 5 | 60 |
- 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.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Production Cost (1000s) | Sales Price (1000s) | Formula | Result |
| 2 | 2 | 20 | =COVARIANCE.S(A2:A4,B2:B4) | 15 |
| 3 | 3 | 30 | ||
| 4 | 4 | 40 |
- 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.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Hours of Operation | Total Sales (1000s) | Formula | Result |
| 2 | 8 | 35 | =COVARIANCE.S(A2:A4,B2:B4) | -1 |
| 3 | 10 | 40 | ||
| 4 | 12 | 45 |
- 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
- Ensure both data sets have the same number of data points.
- 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.
- 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.