Documentation/Calc Functions/COVAR

    From The Document Foundation Wiki

    Function name:

    COVAR

    Category:

    Statistical Analysis

    Summary:

    Calculates the population 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 COVAR 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:

    COVAR(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 COVAR reports a #VALUE! error.
    • If the dimensions of Data 1 and Data 2 are not identical, then COVAR 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 all data in Data 1 and Data 2 is ignored because of empty cells or cells containing text, then COVAR reports a #VALUE! error.

    Additional details:

    • Calc's COVAR and COVARIANCE.P functions perform identical calculations. The requirements for COVAR are specified in ODF 1.2; COVARIANCE.P is provided for interoperability with Microsoft Excel.
    • The equation for COVAR is:
    [math]\displaystyle{ \text{COVAR}(X;\:Y)~=~ \sum_{i=1}^{n}\frac{(x_{i}\:-\:\bar{x})(y_{i}\:-\:\bar{y})}{n} }[/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.
    • Note that covariance is commutative, so that [math]\displaystyle{ \text{COVAR}(X;\:Y)~=~\text{COVAR}(Y;\:X) }[/math].
    • 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 COVAR 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
    =COVAR({1,2,3}; {2,3,4}) Here COVAR returns a positive value indicating that values in the two data sets tend to move in the same direction. 0.666666666666667
    =COVAR({1,2,3}; {-2,-3,-4}) Here COVAR returns a negative value indicating that values in the two data sets tend to move in opposite directions. -0.666666666666667
    =COVAR(A2:A7; B2:B7) using the data above. Here COVAR 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. 165.166666666667
    =COVAR(array3; array4) using the data above, where array3 and array4 are named ranges referring to cells D2:D7 and E2:E7 respectively. Here COVAR 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. -761

    Related LibreOffice functions:

    COVARIANCE.P

    COVARIANCE.S

    ODF standard:

    Section 6.18.18, part 2

    Related (or similar) Excel functions:

    COVAR