Documentation/Calc Functions/COVARIANCE.S

    From The Document Foundation Wiki
    This page contains changes which are not marked for translation.

    Function name:

    COVARIANCE.S

    Category:

    Statistical Analysis

    Summary:

    Calculates the sample covariance of two data sets. Covariance is a statistical metric that is used to investigate the relationship between two random variables. The sign of the value returned by COVARIANCE.S indicates the direction of the linear relationship between the two supplied data sets, while a value of zero implies that there there is no linear relationship between the data sets.

    Syntax:

    COVARIANCE.S(Data 1; Data 2)

    Returns:

    Returns a real number in the range (-∞, +∞), which is the covariance of the two supplied data sets.

    A positive covariance indicates that values tend to move in the same direction, so that greater values of one variable tend to correspond to greater values of the other and lesser values of one variable tend to correspond to lesser values of the other. A negative covariance indicates that values tend to move in opposite directions, so that greater values of one variable tend to correspond to lesser values of the other.

    Arguments:

    Data 1 is an array of real numbers containing the first data set. This argument can take the form of an explicit cell range, the name of a named range, the name of a database range, or an inline constant array.

    Data 2 is an array of real numbers containing the second data set. This argument can take the same forms as those listed for Data 1. Data 2 should have the same dimensions as Data 1.

    • If either Data 1 or Data 2 is not an array, then COVARIANCE.S reports a #VALUE! error.
    • If the dimensions of Data 1 and Data 2 are not identical, then COVARIANCE.S reports an invalid argument error (Err:502).
    • Any cell in Data 1 that is empty or contains text is ignored, along with the corresponding cell in Data 2. Similarly, a cell in Data 2 that is empty or contains text is ignored, along with the corresponding cell in Data 1.
    • If Data 1 and Data 2 do not comprise two or more data points, then COVARIANCE.S reports a #VALUE! error.

    Additional details:

    • COVARIANCE.S is provided for interoperability with Microsoft Excel.
    • The equation for COVARIANCE.S is:
    [math]\displaystyle{ \text{COVARIANCE.S}(X;\:Y)~=~ \sum_{i=1}^{n}\frac{(x_{i}\:-\:\bar{x})(y_{i}\:-\:\bar{y})}{n-1} }[/math]
    where
    • xi are the values in the data set X.
    • is the mean of the values in the data set X.
    • yi are the values in the data set Y.
    • ӯ is the mean of the values in the data set Y.
    • n is the number of data points in the two data sets.
    • From the above equation it can be seen that [math]\displaystyle{ \text{COVARIANCE.S}(X;\:Y)~=~\frac{n}{n-1}\:\times\:\text{COVARIANCE.P}(X;\:Y) }[/math].
    • Note that covariance is commutative, so that [math]\displaystyle{ \text{COVARIANCE.S}(X;\:Y)~=~\text{COVARIANCE.S}(Y;\:X) }[/math].
    • The name space for COVARIANCE.S is COM.MICROSOFT.COVARIANCE.S.
    • For more information about covariance, visit Wikipedia’s Covariance page.

    Examples:

    The following data sets are utilized in two of the examples below.

    Data for COVARIANCE.S examples
    A B C D E
    1 Array 1 Array 2 Array 3 Array 4
    2 195 200 35 20
    3 151 180 7 -61
    4 148 178 -83 20
    5 189 165 11 -55
    6 183 192 -57 -35
    7 154 144 33 -85
    Formula Description Returns
    =COVARIANCE.S({1,2,3}; {2,3,4}) Here COVARIANCE.S returns a positive value indicating that values in the two data sets tend to move in the same direction. 1
    =COVARIANCE.S({1,2,3}; {-2,-3,-4}) Here COVARIANCE.S returns a negative value indicating that values in the two data sets tend to move in opposite directions. -1
    =COVARIANCE.S(A2:A7; B2:B7) using the data above. Here COVARIANCE.S again returns a positive value indicating that values in the two data sets tend to move in the same direction. The magnitude of the value returned is significantly greater here than in the first example – be aware that it is difficult to infer the strength of any relationship based on the magnitude of the covariance since it is not a normalized value. 198.2
    =COVARIANCE.S(array3; array4) using the data above, where array3 and array4 are named ranges referring to cells D2:D7 and E2:E7 respectively. Here COVARIANCE.S returns a negative value indicating that values in the two data sets tend to move in opposite directions. The magnitude of the value returned is significantly greater here than in the second example – note again that it is difficult to infer the strength of any relationship based on the magnitude of the covariance since it is not a normalized value. -913.2

    Related LibreOffice functions:

    COVAR

    COVARIANCE.P

    ODF standard:

    None

    Related (or similar) Excel functions:

    COVARIANCE.S since v.2010.