How to use the COVARIANCE.S function
What is the COVARIANCE.S function?
The COVARIANCE.S function calculates the sample covariance from two different datasets.
What is covariance?
Covariance is the average of the products of deviations for each pair in two different datasets. The covariance is positive if greater values in the first data set correspond to greater values in the second data set. The covariance is negative if greater values in the first data set correspond to smaller values in the second data set.
What is the average of the products of deviations for each pair in two different datasets?
The covariance between two datasets is computed by taking each data point, finding its deviation from its respective dataset mean by subtracting the mean, multiplying the two datasets' deviations together for each pair, and averaging these cross-products of deviations.
What is deviation?
In statistics, deviation is a measure of how far each value in a data set lies from the mean (the average of all values). A high deviation means that the values are spread out widely, while a low deviation means that they are clustered closely around the mean.
What is the mean?
It is also known as the average. It is calculated by adding up all the values in the data set and dividing by the number of values.
For example, if you have a data set of 5, 7, 9, 11, and 13, the mean is (5 + 7 + 9 + 11 + 13) / 5 = 9.
How to interpret covariance?
A positive covariance means that the variables tend to increase or decrease together, indicating a positive linear relationship.
A negative covariance means that the variables tend to move in opposite directions, indicating a negative linear relationship.
A zero covariance means that the variables are independent and have no linear relationship.
However, covariance is not a standardized measure and it depends on the scale and units of the variables. It is not easy to compare the covariances of different pairs of variables or interpret the strength of the relationship. A more common and useful measure of linear relationship is the correlation coefficient, which is the normalized version of covariance.
How to calculate normalized version of covariance?
To calculate the normalized version of covariance, which is also known as the correlation coefficient, you need to divide the covariance by the product of the standard deviations of the two variables. The standard deviation is a measure of how much the values in a data set deviate from the mean.
What is the difference between the COVARIANCE.S function and the COVARIANCE.P function?
The COVARIANCE.S function calculates the covariance based on a sample while the COVARIANCE.P function calculates the covariance based on a population.
COVARIANCE.P = Σ(x- x̄)(y- ȳ)/n
x̄ - AVERAGE(array1)
ȳ - AVERAGE(array2)
n - sample size
COVARIANCE.S Function Syntax
COVARIANCE.S(array1, array2)
COVARIANCE.S Function Arguments
| array1 | Required. The first data set. |
| array2 | Required. The second data set. |
COVARIANCE.S function example
Formula in cell C18:
COVARIANCE.S function not working
Text, logical or empty values are ignored, however, 0 (zeros) are included.
The COVARIANCE.S function returns
- #N/A error value if the number of data points in array1 and array2 is not equal.
- #DIV/0! error value if either array1 or array2 is empty.
Functions in 'Statistical' category
The COVARIANCE.S function function is one of many functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form