Documentation/Calc Functions/CORREL

    From The Document Foundation Wiki

    Function name:

    CORREL

    Category:

    Statistical Analysis

    Summary:

    Calculates the Pearson correlation coefficient to investigate the linear relationship between two variables.

    Syntax:

    CORREL(Data 1; Data 2)

    Returns:

    Returns a real number in the range [-1.0, 1.0], which is the Pearson correlation coefficient for the specified data sets.

    The returned value may be interpreted as follows:

    • A value of 1.0 indicates perfect positive linear correlation – as one variable changes, the other variable changes in the same direction.
    • Other positive values reflect the strength of a positive linear correlation.
    • A value of 0.0 indicates no linear correlation – there is no relationship between the variables.
    • A value of -1.0 indicates perfect negative linear correlation – as one variable changes, the other variable changes in the opposite direction.
    • Other negative values reflect the strength of a negative linear correlation.

    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 CORREL reports a #VALUE! error.
    • If the dimensions of Data 1 and Data 2 are not identical, then CORREL reports an invalid argument error (Err:502).
    • Any cell in Data 1 that is empty or contains non-numeric data is ignored, along with the corresponding cell in Data 2. Similarly, a cell in Data 2 that is empty or contains non-numeric data 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 non-numeric data, then CORREL reports a #VALUE! error.
    • If the standard deviation of the values in either Data 1 or Data 2 is zero then all the values are the same and CORREL reports a #DIV/0! error.

    Additional details:

    • Calc's CORREL and PEARSON functions perform identical calculations.
    • The formula for CORREL is:
    [math]\displaystyle{ r~=~\frac{\sum_{}^{}(x_i-\bar{x})(y_i-\bar{y})}{\sqrt{\sum_{}^{}(x_i-\bar{x})^2\sum_{}^{}(y_i-\bar{y})^2}} }[/math]
    where
    r is commonly used to represent the Pearson correlation coefficient, the value output by CORREL.
    [math]\displaystyle{ x_i }[/math] represent the values in the first array argument.
    [math]\displaystyle{ \bar{x} }[/math] is the mean of the [math]\displaystyle{ x_i }[/math] values.
    [math]\displaystyle{ y_i }[/math] represent the values in the second array argument.
    [math]\displaystyle{ \bar{y} }[/math] is the mean of the [math]\displaystyle{ y_i }[/math] values.
    All summations are over the range 1 to the number of values in the arrays (N).

    Examples:

    The following data sets are utilized in two of the examples below. Note that the examples presented make no statement about the statistical significance of any correlation, which is beyond the scope of CORREL.

    Data for CORREL examples
    A B C D E
    1 Array 1 Array 2 Array 3 Array 4
    2 195 200 0.930 -0.140
    3 151 180 0.300 -0.080
    4 148 178 -0.170 -0.660
    5 189 165 -0.940 0.320
    6 183 192 -0.520 0.900
    7 154 144 0.940 0.860
    Formula Description Returns
    =CORREL({1,2,3}; {2,4,6}) Here CORREL confirms a perfect positive linear correlation between the two data sets. 1
    =CORREL({1,2,3}; {-3,-6, -9}) Here CORREL confirms a perfect negative linear correlation between the two data sets. -1
    =CORREL(A2:A7; B2:B7) using the data above. Here CORREL indicates a moderate positive linear correlation between the two data sets. 0.46706598573232
    =CORREL(array3; array4) using the data above, where array3 and array4 are named ranges referring to cells D2:D7 and E2:E7 respectively. Here CORREL indicates a negligible negative linear correlation between the two data sets. -0.046778661219419

    Related LibreOffice functions:

    PEARSON

    RSQ

    ODF standard:

    Section 6.18.17, part 2

    Related (or similar) Excel functions:

    CORREL