Documentation/Calc Functions/GROWTH

    From The Document Foundation Wiki
    Other languages:

    Function name:

    GROWTH

    Category:

    Array

    Summary:

    Uses supplied data to predict future growth based on an exponential regression. The function can be used in many fields where growth occurs exponentially, for example population growth, epidemiology, finance, and economics.

    GROWTH finds the exponential curve that is the best fit to the supplied data set and returns data points that lie on the general curve [math]\displaystyle{ y(x)=bm^x }[/math], where [math]\displaystyle{ b }[/math] is the value of [math]\displaystyle{ y(0) }[/math] and [math]\displaystyle{ m }[/math] is a constant.

    Syntax:

    GROWTH(Data Y [; [ Data X ] [; [ New Data X ] [; Function Type ] ] ])

    Returns:

    Returns an array of positive decimal numbers that comprise the predicted y-values calculated using an exponential 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.

    Function Type is a Boolean expression, or a reference to a cell containing a Boolean expression, that determines whether the constant [math]\displaystyle{ b }[/math] of the exponential curve is set to 1. If Function Type is FALSE or zero, [math]\displaystyle{ b }[/math] is set to 1. If Function Type is TRUE, non-zero, or omitted, [math]\displaystyle{ b }[/math] is calculated from the known data.

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

    Additional details:

    Details specific to GROWTH function

    • Although the term "growth" is used throughout this wiki page, depending on the input data it is possible for the GROWTH function to return values that represent decay rather than growth.
    • More background information can be found at Wikipedia's Exponential growth 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
    {=GROWTH({1,2,4,8,16}; {0,1,2,3,4}; {5,6})} entered as an array formula after selecting cell A1. For simplicity, the values used in this example have been chosen to follow the exponential curve [math]\displaystyle{ y(x)~=~2^x }[/math]. 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). GROWTH utilizes these number pairs to find the best-fit exponential curve and then uses that curve to predict the y-values for the two x-values listed in the third argument (New Data X). The formula cell (A1) displays the value 32, while cell A2 displays the value 64. The Function Type argument is omitted and defaults to TRUE, but its value is not significant in this example. See Description
    {=GROWTH(A1:A4)} entered as an array formula after selecting cell A6, with cells A1 to A4 containing the numbers 3, 6, 9, and 12 respectively. Here only the first argument (Data Y) is supplied. In this case Data X defaults to the array {1; 2; 3; 4}, New Data X defaults to the same array, and Function Type defaults to TRUE. GROWTH utilizes the Data X and Data Y number pairs to find the best-fit exponential curve, which turns out to be [math]\displaystyle{ y(x)~=~2.12132034355964*1.57843652999765^x }[/math]. As a result, cells A6 to A9 display the values 3.34836952210171, 5.28518876961613, 8.34233502189546, and 13.1678463440386. See Description
    {=GROWTH(A1:A4; ; ; FALSE)} entered as an array formula after selecting cell A6, with cells A1 to A4 containing the numbers 3, 6, 9, and 12 respectively. This example is similar to the previous one, apart from setting the Function Type argument to FALSE. This forces GROWTH to set the constant [math]\displaystyle{ b }[/math] of the exponential curve to 1 and results in a best fit curve of [math]\displaystyle{ y(x)~=~2.02813040367418^x }[/math]. The values 2.02813040367418, 4.11331293430758, 8.34233502189545, and 16.9193432955421 are displayed in cells A6 to A9. See Description
    {=GROWTH(population; known_years; {6; 7}; TRUE())} entered as an array formula after selecting cell A9. population is a named range covering cells A2 to A7, which contain the values 7379797, 7464022, 7547859, 7631091, 7713468, and 7794799 respectively. known_years is a named range covering cells B2 to B7, which contain the values 0, 1, 2, 3, 4, and 5 respectively. This exampled is configured with UN data to provide a simple example relating to population growth. The values in cells A2 to A7 are the estimated total world populations in years 2015 to 2020 inclusive. The values in cells B2:B7 are year offsets from 2015 and so the inline constant array {6; 7} instructs GROWTH to predict the populations for 2021 and 2022. The values 7883516 and 7970289 are displayed in cells A9 and A10. See Description

    Related LibreOffice functions:

    TREND

    ODF standard:

    Section 6.18.35, part 2

    Related (or similar) Excel functions:

    GROWTH