The `COVARIANCE.S` function in Excel is used to calculate the sample covariance between two sets of values. Covariance measures how much two random variables change together, and is an indicator of the strength and direction of their linear relationship.
Syntax:
COVARIANCE.S(array1, array2)
- array1: The first range or array of integer data.
- array2: The second range or array of integer data.
Steps to Use `COVARIANCE.S`:
- Prepare Your Data:
- Ensure that you have two sets of numerical data with the same number of values.
- For instance, you might have one column for set `X` and another for set `Y`.
- Select the Cell:
- Click on the cell where you want to display the covariance result.
- Enter the Function:
- Type `=COVARIANCE.S(` into the formula bar.
- Input Your Arrays:
- Highlight the first set of data or type the range directly (e.g., `B2:B10` for `array1`).
- Apply a comma.
- Highlight the second set of data or type the range directly (e.g., `C2:C10` for `array2`).
- Close the Formula:
- Finish with a closing parenthesis `)` and press `Enter`.
Example:
Assume that your data for `X` is in cells `B2:B10` and your data for `Y` is in cells `C2:C10`. You would use the formula:
=COVARIANCE.S(B2:B10, C2:C10)
Additional Considerations:
- Both arrays must contain the same number of data points, otherwise, the function will return a `#N/A` error.
- If either `array1` or `array2` contains non-numeric data, Excel will return an error.
- Use `COVARIANCE.P` if you need to calculate the covariance for an entire population instead of a sample.
By following these steps, you can effectively use the `COVARIANCE.S` function to determine the relationship between two data sets in your Excel workbook.
Unlock Your Potential
Excel
Access
Power BI
