Documentation/Calc Functions/LINEST

    From The Document Foundation Wiki

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:


    Function name:

    LINEST

    Category:

    Array

    Summary:

    Calculates the straight line that best fits a supplied data set and optionally returns detailed statistics on the regression.

    Syntax:

    LINEST(Data Y [; Data X [; Linear type [; Stats]]])

    Returns:

    Returns an array of decimal numbers that are the statistics for a straight line that best fits the input data set. The dimensions of the returned array depend on the nature of the arguments.

    Arguments:

    Data Y contains the set of known y-values to be used to determine the regression line. This argument can take the form of an explicit cell range (such as A1:C100), 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 to be used to determine the regression equation. This argument can take the form of an explicit cell range (such as D1:D100), the name of a named range, the name of a database range, or an inline constant array. If omitted, it is set to the array {1, 2, 3, …, k}, where k is the number of values in Data Y. If there is more than one set of variables, Data X may be a range with corresponding multiple rows or columns.

    Linear type is a Boolean expression, or a reference to a cell containing a Boolean expression, that determines whether the regression line is forced to pass through the origin. If Linear type is FALSE or zero, the straight line found is forced to pass through the origin. If Linear type is TRUE, non-zero, or omitted, the line is not forced to pass through the origin.

    Stats is a Boolean expression, or a reference to a cell containing a Boolean expression, that determines whether the full statistics table is returned or just the top line. If Stats is FALSE, zero, or omitted, only the top line of the statistics table is returned. If Stats is TRUE or non-zero, the full statistics table is returned.

    • If any entry in Data Y or Data X is non-numeric, then LINEST reports an invalid argument error (Err:502).
    • If Data Y is neither a single-column vector nor a single-row vector, then Data Y and Data X must have the same dimensions. If this condition is not met, then LINEST reports an invalid argument error (Err:502).
    • If Data Y is a single-column vector, then Data Y and Data X must have the same number of rows. If this condition is not met, then LINEST reports an invalid argument error (Err:502).
    • If Data Y is a single-row vector, then Data Y and Data X must have the same number of columns. If this condition is not met, then LINEST reports an invalid argument error (Err:502).

    Additional details:

    Details specific to LINEST function

    • More background information can be found at Wikipedia's Linear regression page and in the sources that it references.
    • The following table illustrates the array of statistics returned when the Stats argument is set TRUE. The top left cell of the table corresponds to the top-left cell of the cell area in which the array formula was entered. It is assumed that with [math]\displaystyle{ n }[/math] sets of variables, the straight line is of the form [math]\displaystyle{ y=a~+~b_{1}x_{1}~+~b_{2}x_{2}~+~...~+~b_{n}x_{n} }[/math].
    Array of statistics returned by LINEST function
    [math]\displaystyle{ {b}_{n} }[/math] [math]\displaystyle{ {b}_{n-1} }[/math] ... [math]\displaystyle{ {b}_{1} }[/math] [math]\displaystyle{ a }[/math]
    [math]\displaystyle{ {\sigma }_{n} }[/math] [math]\displaystyle{ {\sigma }_{n-1} }[/math] ... [math]\displaystyle{ {\sigma }_{1} }[/math] [math]\displaystyle{ {\sigma }_{a} }[/math]
    [math]\displaystyle{ {r}^{2} }[/math] [math]\displaystyle{ {\sigma }_{y} }[/math]
    [math]\displaystyle{ F }[/math] [math]\displaystyle{ \mathit{df} }[/math]
    [math]\displaystyle{ {\mathit{SS}}_{\mathit{reg}} }[/math] [math]\displaystyle{ {\mathit{SS}}_{\mathit{resid}} }[/math]
    • [math]\displaystyle{ {b}_{1} }[/math] to [math]\displaystyle{ {b}_{n} }[/math] are the line gradients or slopes. [math]\displaystyle{ n }[/math] is the number of sets of variables contained in the Data X argument. These slope values are given in reverse order; that is, the slope for the first set of variables is given last and vice-versa.
    • [math]\displaystyle{ a }[/math] is the y-axis intercept.
    • [math]\displaystyle{ {\sigma }_{1} }[/math] to [math]\displaystyle{ {\sigma }_{n} }[/math] are the standard error values for the line gradients.
    • [math]\displaystyle{ {\sigma }_{a} }[/math] is the standard error value for the y-axis intercept.
    • [math]\displaystyle{ {r}^{2} }[/math] is the determination coefficient.
    • [math]\displaystyle{ {\sigma }_{y} }[/math] is the standard error value for the y estimate.
    • [math]\displaystyle{ F }[/math] is the F statistic (F-observed value).
    • [math]\displaystyle{ \mathit{df} }[/math] is the number of degrees of freedom.
    • [math]\displaystyle{ {\mathit{SS}}_{\mathit{reg}} }[/math] is the regression sum of squares.
    • [math]\displaystyle{ {\mathit{SS}}_{\mathit{resid}} }[/math] is the residual sum of squares.
    Empty cells in this table contain the #N/A error.
    When the Stats argument is set FALSE, only the top line is returned (containing [math]\displaystyle{ {b}_{1} }[/math] to [math]\displaystyle{ {b}_{n} }[/math] and [math]\displaystyle{ a }[/math]).


    General information about Calc's array functions

    Note pin.svg

    Note:
    For convenience, the information in this subsection is repeated on all pages describing Calc's array functions.

    To avoid repetition within the subsequent text, only Windows/Linux key combinations are specified. The following table shows the mapping between these key combinations and their macOS equivalents.

    Mapping between relevant Windows/Linux and macOS key combinations
    Windows/Linux key combination macOS equivalent Comments
    Ctrl + ⇧ Shift + ↵ Enter ⌘ Cmd + ⇧ Shift + ↵ Enter Create an array formula.
    Ctrl + / ⌘ Cmd + / Select all cells in an array formula range. "/" is the division key on the numeric keypad.
    Ctrl + C ⌘ Cmd + C Copy.
    Ctrl + V ⌘ Cmd + V Paste.
    • An array is a rectangular range of cells containing data. For example, a square range of three rows by three columns is a 3 x 3 array. The smallest possible array is a 1 x 2 or 2 x 1 array, with two adjacent cells.
    • A formula in which the individual values in a cell range are evaluated is referred to as an array formula. The difference between an array formula and other formulas is that the array formula deals with several values simultaneously instead of just one.
    • If you create an array formula using the Function Wizard, you must mark the Array check box so that the results are returned in an array. Otherwise, only the value in the upper-left cell of the array being calculated is returned.
    • If you enter the array formula directly into the cell, you must use the key combination Ctrl + ⇧ Shift + ↵ Enter instead of just the ↵ Enter key. Only then does the formula become an array formula.
    • An array formula is displayed inside curly brackets or braces ("{" and "}"). It is not possible to create an array formula by manually entering these braces.
    • Some array functions take parameters that are forced to evaluate as an array formula, even when the formula is entered normally. These are MDETERM, MINVERSE, MMULT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, and SUMXMY2.
    • Calc supports constant inline arrays in formulas. An inline array is enclosed within curly brackets or braces ("{" and "}"). Individual elements can be numbers (including negatives), logical constants (TRUE, FALSE), or literal strings. Non-constant expressions are not allowed. Arrays can be entered with one or more rows, and one or more columns. All rows must comprise the same number of elements; similarly, all columns must comprise the same number of elements.
    • Editing array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Then press F2 or position the cursor in the Input line, edit the formula as required, and use the key combination Ctrl + ⇧ Shift + ↵ Enter.
    • Deleting array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Press Delete to delete the array contents, including the array formula. Alternatively, press ← Backspace to bring up the Delete Contents dialog box, select Formulas or Delete all, and click OK.
    • Copying array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Then press F2 or position the cursor in the Input line. Copy the formula by pressing Ctrl + C. Select a range of cells where you want to insert the array formula and either press F2 or position the cursor in the Input line. Paste the formula by pressing Ctrl + V in the selected space and confirm it using the key combination Ctrl + ⇧ Shift + ↵ Enter. The selected range now contains the array formula.
    • Adjusting an array range. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Below the selection, to the right, you will see a small icon with which you can zoom in or out on the range using your mouse. When you adjust the array range, the array formula will not be adjusted automatically - you are only changing the range in which the results will appear.

    Examples:

    Formula Description Returns
    =LINEST({3,5,7,9,11}) entered as an array formula after selecting cell A1. For simplicity, the values used in this example have been chosen to follow the line [math]\displaystyle{ y(x)~=~2x~+~1 }[/math]. Here only the first argument (Data Y) is supplied and in this case Data X defaults to the array {1, 2, 3, 4, 5}. The array formula returns the value 2 in cell A1 and the value 1 in cell B1. See Description
    =LINEST(A1:A5; B1:B5) entered as an array formula after selecting cell A7. Cells A1 to A5 contain the numbers 3, 5, 7, 9, and 11 respectively; cells B1 to B5 contain the numbers 2, 3, 4, 5, and 6 respectively. Here five known x-values are listed in the second argument (Data X), while five corresponding known y-values are listed in the first argument (Data Y). The array formula returns the value 2 in cell A7 and the value -1 in cell B7, implying the regression line [math]\displaystyle{ y(x)~=~2x~-~1 }[/math]. See Description
    =LINEST(y_values; x_values; ; TRUE) entered as an array formula after selecting cell A7. y_values is a named range covering cells A1:A5 and x_values is a named range covering cells B1:B5. As in the previous example, cells A1 to A5 contain the numbers 3, 5, 7, 9, and 11 respectively, while cells B1 to B5 contain the numbers 2, 3, 4, 5, and 6 respectively. In this example, LINEST processes the same data as in the previous example but in this case the Stats argument is set to TRUE. The array formula populates cells in the range A7:B11 with the full statistics table, as follows:
    • 2 (A7)
    • -1 (B7)
    • 0 (A8)
    • 0 (B8)
    • 1 (A9)
    • 0 (B9)
    • #N/A (A10)
    • 3 (B10)
    • 40 (A11)
    • 0 (B11)
    		 See Description
    =LINEST(y_values; x_values; FALSE) entered as an array formula after selecting cell A7. y_values is a named range covering cells A1:A5 and x_values is a named range covering cells B1:B5. As in the previous two examples, cells A1 to A5 contain the numbers 3, 5, 7, 9, and 11 respectively, while cells B1 to B5 contain the numbers 2, 3, 4, 5, and 6 respectively. In this example, LINEST processes the same data as in the previous two examples but in this case the Linear type argument is set to FALSE, forcing the regression line to go through the origin. The array formula returns the value 1.77777777777778 in cell A7 and the value 0 in cell B7. See Description

    The subsequent example uses the following spreadsheet data, in which the data input to LINEST includes two x-variables. The cells containing the output values from LINEST are limited to display only four decimal places (to reduce visual clutter).

    Sample LINEST data with two x-variables
    A B C D E F G
    1 x1 x2 y
    2 4 7 100 4.1667 −3.4762 82.3333
    3 5 9 105 5.4614 10.9644 9.3461
    4 6 11 104 0.8685 5.0562 #N/A
    5 7 12 108 13.2102 4.0000 #N/A
    6 8 15 111 675.4524 102.2619 #N/A
    7 9 17 120
    8 10 19 133
    Formula Description Returns
    =LINEST(C2:C8; A2:B8; ; TRUE) entered as an array formula after selecting cell E2. Here the function returns the full table of statistics in the cell range E2:G6. These results indicate that the regression line is [math]\displaystyle{ y~=~82.3333~-~3.4762x_{1}~+~4.1667x_{2} }[/math]. See Description

    Related LibreOffice functions:

    LOGEST

    ODF standard:

    Section 6.18.41, part 2

    Related (or similar) Excel functions:

    LINEST


    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/Calc_Functions/LINEST&oldid=729333"