Documentation/Calc Functions/STEYX

    From The Document Foundation Wiki

    Function name:

    STEYX

    Category:

    Statistical Analysis

    Summary:

    Calculates the standard error for a simple linear regression of the supplied data. This single value represents the average distance between the supplied y-values and the corresponding y-values on the regression line, indicating how well the regression line matches the data.

    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:

    STEYX(DataY; DataX)

    Returns:

    Returns a non-negative real number, which is the standard error 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 STEYX 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 Data X and Data Y contain less than three valid pairs of values, then STEYX reports a #VALUE! error.

    Additional details:

    • The formula for the standard error of estimate ([math]\displaystyle{ S_e }[/math]) calculated by STEYX is:
    [math]\displaystyle{ S_e\:=\:\sqrt{\frac{1}{(n-2)} \left(\sum_{i=1}^{n}(y_i-\bar{y})^2 ~-~ \frac{\left(\displaystyle\sum_{i=1}^{n}(x_i-\bar{x})(y_i-\bar{y})\right)^2}{\displaystyle\sum_{i=1}^{n}(x_i-\bar{x})^2}\right)} }[/math]
    where
    • [math]\displaystyle{ n }[/math] is the number of valid data points (x-y pairs) within the Data X and Data Y arrays
    • [math]\displaystyle{ \bar{x} }[/math] is the mean of the x values in the [math]\displaystyle{ n }[/math] data points
    • [math]\displaystyle{ \bar{y} }[/math] is the mean of the y values in the [math]\displaystyle{ n }[/math] data points
    • [math]\displaystyle{ x_i }[/math] is the x value of the i-th data point
    • [math]\displaystyle{ y_i }[/math] is the y value of the i-th data point
    • For more information on standard errors, visit Wikipedia's Standard error page.

    Examples:

    The following table contains sample data for the STEYX function which is used in the final 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
    =STEYX({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], and so the standard error is 0. 0
    =STEYX({6.2,7.3,9.4}; {1.7,2.8,3.9}) Here the three x-y data points do not lie on a straight line and so a non-zero standard error is returned. 0.408248290463864
    =STEYX(A2:A7; B2:B7) where cells A2:B7 contain the data shown in the table above. Here the function calculates the standard error of the best-fit regression line for the supplied data passed as references to ranges of cells. 20.9991200254713

    Related LibreOffice functions:

    INTERCEPT

    LINEST

    SLOPE

    ODF standard:

    Section 6.18.76, part 2

    Related (or similar) Excel functions:

    STEYX