Documentation/Calc Functions/TREND

    From The Document Foundation Wiki
    Other languages:


    Function name:

    TREND

    Category:

    Array

    Summary:

    Fits a straight line to supplied data using linear regression and returns an array of y-values that lie on that line.

    Syntax:

    TREND(Data Y [; Data X [; New Data X [; Linear Type]]])

    Returns:

    Returns an array of real numbers that comprise the predicted y-values calculated using a linear regression. 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 equation. 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.

    New Data X contains the set of x-values for which predicted y-values are to be calculated. This argument can take the form of an explicit cell range (such as E1:E10), the name of a named range, the name of a database range, or an inline constant array. If omitted, New Data X is set to Data X.

    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.

    • If any entry in Data Y, Data X, or New Data X is non-numeric, then TREND 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 TREND 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 TREND 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 TREND reports an invalid argument error (Err:502).

    Additional details:

    Details specific to TREND function

    • More background information can be found at Wikipedia's Linear regression page and in the sources that it references.


    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
    {=TREND({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} and New Data X defaults to the same array. The array formula populates cells A1 to E1 with the numbers 3, 5, 7, 9, and 11 respectively. See Description
    {=TREND(A1:A5; B1:B5)} entered as an array formula after selecting cell A7. Cells A1 to A5 contain the numbers 3.1, 4.9, 7.1, 8.9, and 11.1 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 populates the cells A7 to A11 with the numbers 3.02, 5.02, 7.02, 9.02, and 11.02; these values are based on the regression line [math]\displaystyle{ y(x)~=~2x~-~0.98 }[/math] that is calculated from the original data points. See Description
    {=TREND(y_values; x_values; {-1,0,1,7,8})} 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. 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. In this example, TREND calculates the regression line based on the y-values and x-values given in the first two arguments (this will be the line [math]\displaystyle{ y(x)~=~2x~-~1 }[/math]). The function then calculates the y-value for each of the x-values given in the third argument. The array formula populates cells A7 to E7 with the numbers -3, -1, 1, 13, and 15 respectively. Note that if the x-values represent times, then the results include both past and future data. See Description
    {=TREND({3,5,7,9,11}; ; ; FALSE)} entered as an array formula after selecting cell A1. Here the Linear Type argument is set FALSE, forcing the regression line to go through the origin rather than allowing TREND to determine the best fit. Internally, TREND will calculate the regression line as [math]\displaystyle{ y(x)~=~2.27272727272727x }[/math]. The array formula populates cells A1 to E1 with the numbers 2.27272727272727, 4.54545454545455, 6.81818181818182, 9.09090909090909, and 11.3636363636364 respectively. See Description

    The subsequent example uses the following spreadsheet data, in which the data input to TREND includes two x-variables. The page describing the LINEST function demonstrates that the regression line for this input data is [math]\displaystyle{ y~=~82.3333~-~3.4762x_{1}~+~4.1667x_{2} }[/math] (with coefficients in the equation limited to only four decimal places).

    Sample TREND data with two x-variables
    A B C
    1 x1 x2 y
    2 4 7 100
    3 5 9 105
    4 6 11 104
    5 7 12 108
    6 8 15 111
    7 9 17 120
    8 10 19 133
    Formula Description Returns
    {=TREND(C2:C8; A2:B8; {11,21; 12,22})} entered as an array formula after selecting cell A10. Here the function returns the y-value 131.595238095238 (x1=11 and x2=21) in cell A10 and the y-value 132.285714285714 (x1=12 and x2=22) in cell A11. It can be checked that these value lie (approximately) on the regression line calculated from the same reference data points on the LINEST page (noting that there the coefficients in the regression line equation are only displayed to four decimal places). See Description

    Related LibreOffice functions:

    GROWTH

    INTERCEPT

    SLOPE

    STEYX

    ODF standard:

    Section 6.18.79, part 2

    Related (or similar) Excel functions:

    TREND