Documentation/Calc Functions/SLOPE

    From The Document Foundation Wiki

    Function name:

    SLOPE

    Category:

    Statistical Analysis

    Summary:

    Uses simple linear regression to calculate the least squares regression line for the supplied data, returning the slope of that line.

    The x-data are values of the predictor, explanatory, or independent variable. The y-data are corresponding values of the response, outcome, or dependent variable.

    Syntax:

    SLOPE(Data Y; Data X)

    Returns:

    Returns a real number, which is the slope of the best-fit regression line for the supplied data.

    Arguments:

    Data Y contains the set of known y-values (dependent observations or data) to be used to determine the regression line. 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 contains the set of known x-values (independent observations or data) to be used to determine the regression line. 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 should have the same dimensions as Data Y.

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

    Additional details:

    • The equation of a straight line may be written as [math]\displaystyle{ y\:=\:\alpha \:+\: \beta x }[/math]. [math]\displaystyle{ \beta }[/math] is the slope returned by SLOPE. The parameters of the linear regression line are calculated as follows:
    [math]\displaystyle{ \beta~=~\frac{\displaystyle \sum_{i=1}^{n}{(x_{i}-\bar{x})(y_i-\bar{y})}}{\displaystyle \sum_{i=1}^{n}(x_i-\bar{x})^2} }[/math]
    and
    [math]\displaystyle{ \alpha\:=\: \bar{y}- \beta \bar{x} }[/math]
    Here [math]\displaystyle{ \bar{x} }[/math] and [math]\displaystyle{ \bar{y} }[/math] are the mean values for Data X and Data Y respectively.

    Examples:

    The following table contains sample data for the SLOPE function which is used in the second row of the subsequent table.

    A B
    1 Data Y Data X
    2 195 200
    3 151 180
    4 148 178
    5 189 165
    6 183 192
    7 154 144
    Formula Description Returns
    =SLOPE({5,7,9}; {1,2,3}) In this simple example using inline constant arrays, the three x-y data points lie on the straight line [math]\displaystyle{ y\:=\:3\:+\:2x }[/math], for which the intercept on the y-axis is 3 and the slope is 2. Note that the formula =INTERCEPT({5,7,9}; {1,2,3}) returns the value 3. 2
    =SLOPE(A2:A7; B2:B7) Calculates the slope for the regression line calculated using the x-y data specified in the table above. 0.496617389125532
    =SLOPE({8,9,10,11}; {50,80,110,140}) Calculates the slope for the regression line calculated using the x-y data specified in inline constant arrays. 0.0333333333333333
    =SLOPE(D1:D5; C1:C5) where cells C1:C5 contain the values 1, 2, 3, 4, and 5, while cells D1:D5 contain the values 2, 4, 7, 8, and 10. Here the equation of the regression line is close to [math]\displaystyle{ y\:=\:2x }[/math] (and would be exactly that if cell B4 contained the value 6). 2

    Related LibreOffice functions:

    INTERCEPT

    LINEST

    ODF standard:

    Section 6.18.69, part 2

    Related (or similar) Excel functions:

    SLOPE