Documentation/Calc Functions/RSQ

    From The Document Foundation Wiki

    Function name:

    RSQ

    Category:

    Statistical Analysis

    Summary:

    Calculates the square of the Pearson correlation coefficient between two sets of data.

    The Pearson correlation coefficient expresses the strength of the linear relationship between two variables and is conventionally denoted by the letter r. Its square is then r2 - hence the function name of RSQ.

    Syntax:

    RSQ(Data Y; Data X)

    Returns:

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

    Arguments:

    Data Y 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 X is an array of real numbers containing the second data set. This argument can take the same forms as those listed for Data Y. Data X should have the same dimensions as Data Y.

    • If either Data Y or Data X is not an array, then RSQ reports a #VALUE! error.
    • If the dimensions of Data Y and Data X are not identical, then RSQ reports an invalid argument error (Err:502).
    • Any cell in Data Y that is empty or contains non-numeric data is ignored, along with the corresponding cell in Data X. Similarly, a cell in Data X that is empty or contains non-numeric data is ignored, along with the corresponding cell in Data Y.
    • If all data in Data Y and Data X is ignored because of empty cells or cells containing non-numeric data, then RSQ reports a #VALUE! error.
    • If the standard deviation of the values in either Data Y or Data X is zero then all the values are the same and RSQ reports a #DIV/0! error.

    Additional details:

    • Use Calc's CORREL and PEARSON functions to calculate the Pearson correlation coefficient between two sets of data.
    • The formula for the Pearson correlation coefficient, r, 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
    [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).
    • Then the formula for RSQ is [math]\displaystyle{ {{Calc_fn_name|RSQ|{{PAGELANGUAGE}}}}~=~r^2 }[/math].
    • RSQ indicates how much of the variance in one variable is attributable to the variance in the other variable. r2 is an example of a coefficient of determination.

    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 RSQ.

    Data for RSQ 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
    =RSQ({1,2,3}; {2,4,6}) Here RSQ determines that all variation in one variable is explained by that in the other variable. Note that the formula =PEARSON({1,2,3}; {2,4,6}) also returns 1. 1
    =RSQ({1,2,3}; {-3,-6, -9}) Here RSQ again determines that all variation in one variable is explained by that in the other variable. Note that the formula =PEARSON({1,2,3}; {-3,-6, -9}) returns -1. 1
    =RSQ(A2:A7; B2:B7) using the data above. Here RSQ determines that approximately 20% of the variation in one variable is explained by that in the other variable. Note that the formula =PEARSON(A2:A7; B2:B7) returns 0.46706598573232. 0.218150635028104
    =RSQ(array3; array4) using the data above, where array3 and array4 are named ranges referring to cells D2:D7 and E2:E7 respectively. Here RSQ determines that a tiny percentage of the variation in one variable is explained by that in the other variable. Note that the formula =PEARSON(array3; array4) returns -0.046778661219419. 0.00218824314548117

    Related LibreOffice functions:

    CORREL

    PEARSON

    ODF standard:

    Section 6.18.66, part 2

    Related (or similar) Excel functions:

    RSQ