Documentation/Calc Functions/FORECAST.LINEAR

    From The Document Foundation Wiki

    Function name:

    FORECAST.LINEAR

    Category:

    Statistical Analysis

    Summary:

    Uses least-squares simple linear regression to fit a straight line to known pairs of x-y values. This regression line is then used to calculate an unknown y-value for a specified x-value.

    FORECAST.LINEAR can be used to extrapolate future values based on existing x-y data.

    Syntax:

    FORECAST.LINEAR(Value; Data Y; Data X)

    Returns:

    Returns a real number, which is the y-value that lies on the regression line for the given x-value.

    Arguments:

    Value is a real number, or a reference to a cell containing that number, which is the x-value for which the unknown y-value is to be calculated.

    Data Y is an array of real numbers containing the known y-values. 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 known x-values. 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 must have the same dimensions as Data Y. There is no limitation on the order of the values in Data X and, in particular, they need not be in ascending order. No error is raised if the same value appears multiple times in Data X, even when each occurrence has a different associated y-value in Data Y.

    • If Value is non-numeric, then FORECAST.LINEAR reports a #VALUE! error.
    • If either Data Y or Data X is not a multi-cell array, then FORECAST.LINEAR reports a #VALUE! error.
    • If the dimensions of Data Y and Data X are not identical, then FORECAST.LINEAR reports an invalid argument error (Err:502).
    • If any cell in either array is empty or contains non-numeric data, then that cell is ignored along with the corresponding cell in the other array.
    • If all cells in the arrays are ignored, then FORECAST.LINEAR reports a #VALUE! error.
    • If Data X has zero variance, then FORECAST.LINEAR reports a #DIV/0! error. This will occur when the two arrays each contain only one numeric x-y pair.

    Additional details:

    • Calc's FORECAST and FORECAST.LINEAR functions perform identical calculations. The requirements for FORECAST are specified in ODF 1.2; FORECAST.LINEAR is provided for interoperability with Microsoft Excel.
    • FORECAST.LINEAR finds the regression line [math]\displaystyle{ y~=~\alpha\:+\:\beta x }[/math] where:
    [math]\displaystyle{ \alpha~=~\bar{y}\:-\:\beta\bar{x} }[/math]
    [math]\displaystyle{ \beta~=~\frac{\sum{(x-\bar{x})(y-\bar{y})}}{\sum(x-\bar{x})^2} }[/math]
    [math]\displaystyle{ \bar{x} }[/math] and [math]\displaystyle{ \bar{y} }[/math] are the means of the x-values and y-values respectively.
    • The name space for FORECAST.LINEAR is COM.MICROSOFT.FORECAST.LINEAR.
    • For more information about simple linear regression, visit Wikipedia's Simple linear regression page.

    Examples:

    The data in the following table is utilized in the final example below.

    A B C
    1 Data X Data Y Value
    2 4 36 15
    3 2 91
    4 9 25
    5 10 38
    6 6 80
    7 7 64
    8 1 42
    9 7 39
    10 4 63
    Formula Description Returns
    =FORECAST.LINEAR(10; {4, 6, 8}; {1, 2, 3}) Here FORECAST.LINEAR calculates the regression line as [math]\displaystyle{ y~=~2x+2 }[/math] and returns the y-value that corresponds to x=10. 22
    =FORECAST.LINEAR(170; {8, 9, 10, 11}; {50, 80, 110, 140}) Here FORECAST.LINEAR calculates the regression line as [math]\displaystyle{ y~=~0.03333x + 6.33333 }[/math] and returns the y-value that corresponds to x=170. 12
    =FORECAST.LINEAR(C1; B1:B4; A1:A4) where cells A1:A4 contain the dates 2023-01-01, 2023-02-01, 2023-03-01, and 2023-04-01 respectively; cells B1:B4 contain the numbers 1, 5, 9, and 11 respectively; and cell C1 contains the date 2023-05-01. Here FORECAST.LINEAR is given data for the first four months of the year and predicts the value expected for the fifth month of the same year. 15.0434488968933
    =FORECAST.LINEAR(C2; B2:B10; A2:A10) where the data is as shown in the above table. Here FORECAST.LINEAR calculates the regression line as [math]\displaystyle{ y = -3.09281x + 70.29341 }[/math] and returns the y-value that corresponds to x=15. 23.9011976047904

    Related LibreOffice functions:

    FORECAST

    FORECAST.ETS.ADD

    FORECAST.ETS.MULT

    FORECAST.ETS.PI.ADD

    FORECAST.ETS.PI.MULT

    FORECAST.ETS.SEASONALITY

    FORECAST.ETS.STAT.ADD

    FORECAST.ETS.STAT.MULT

    ODF standard:

    None

    Related (or similar) Excel functions:

    FORECAST.LINEAR