How to use GROWTH Function in Excel

Growth formula in Excel is a statistical function. Growth formula returns the predicted exponential growth rate based on existing values given in excel. It is found under Formulas<More Functions<Statistical<Growth. It is a worksheet function. Growth formula is available in all the versions of Excel. This function is used for statistical and financial analysis. It helps in predicting and forecasting revenues and sales targets of companies. Growth formula is also used in Regression analysis in Excel.

In simple terms, growth function calculates Y values on the basis of an exponential growth rate of given X values.

Syntax:= GROWTH(known_y’s, [known_x’s], [new_x’s], [const])

The GROWTH function syntax has the following arguments:

  1. Known_y’s    Required. The set of y-values you already know in the relationship y = b*m^x.
    • If the array known_y’s is in a single column, then each column of known_x’s is interpreted as a separate variable.
    • If the array known_y’s is in a single row, then each row of known_x’s is interpreted as a separate variable.
    • If any of the numbers in known_y’s is 0 or negative, GROWTH returns the #NUM! error value.
  2. Known_x’s    Optional. An optional set of x-values that you may already know in the relationship y = b*m^x.
    • The array known_x’s can include one or more sets of variables. If only one variable is used, known_y’s and known_x’s can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y’s must be a vector (that is, a range with a height of one row or a width of one column).
    • If known_x’s is omitted, it is assumed to be the array {1,2,3,…} that is the same size as known_y’s.
  3. New_x’s    Optional. Are new x-values for which you want GROWTH to return corresponding y-values.
    • New_x’s must include a column (or row) for each independent variable, just as known_x’s does. So, if known_y’s is in a single column, known_x’s and new_x’s must have the same number of columns. If known_y’s is in a single row, known_x’s and new_x’s must have the same number of rows.
    • If new_x’s is omitted, it is assumed to be the same as known_x’s.
    • If both known_x’s and new_x’s are omitted, they are assumed to be the array {1,2,3,…} that is the same size as known_y’s.
  4. Const    Optional. A logical value specifying whether to force the constant b to equal 1.
    • If const is TRUE or omitted, b is calculated normally.
    • If const is FALSE, b is set equal to 1 and the m-values are adjusted so that y = m^x.

READ:  How to use HARMEAN Function in Excel

Example: Let’s look at some Excel GROWTH function examples and explore how to use the GROWTH function as a worksheet function in Microsoft Excel:

Example 1:

Suppose there a company that has revenue for the previous 12 years. Column A has the years mentioned and column B contains the revenue for each given year. We want to calculate the revenue for the upcoming year. Based on the previously given data we want to calculate the estimated revenue for the year 2020.

GROWTH Function - How to use GROWTH Function in Excel

To predict the revenue for the year 2020, we will be using the GROWTH formula in excel. In this case, the new X value is the upcoming year which is 2020

The GROWTH Formula in Excel that we will be using will be

Syntax:  =GROWTH(B2:B13,A2:A13,A14)

Result: 4031662.45

GROWTH Function in Excel - How to use GROWTH Function in Excel

So, in the year 2020, the company most probably will generate revenue of around $4031662.45

Example 2:

Continuing with the same example, if we wish to find out the revenue that is more than one new y-value, we need to use the array formula.The function should be entered as an array formula.

For this, we will enter an array formula, then highlight the range of cells for the function result.

GROWTH Function 1 - How to use GROWTH Function in Excel

First, we will type the function into the first cell of range:

Syntax:  =GROWTH(B2:B13,A2:A13,A14)

Then press CTRL-SHIFT-Enter, as shown below:

GROWTH Function in Excel 1 - How to use GROWTH Function in Excel

We get the result below:

Result: 4031662.45
4055472.618
4079423.405
4103515.64
4127750.159

As you can see above, the GROWTH function was entered into cells B14-B18 and the function in the formula bar is encased in curly braces { }. It indicates that the function was entered as an array formula.

READ:  How to use AVERAGEA Function in Excel

Example 3:

Suppose in a lab we have an organic solution that contains bacteria that are growing exponentially in the solution. They are increasing with the increase of time given in seconds. We have the sample data for a number of bacteria with given time periods in seconds.  We need to estimate the bacteria growth after 165 seconds.

Column A contains the time values in seconds and column B contains the number of bacteria that are multiplying exponentially.

GROWTH Function 3 - How to use GROWTH Function in Excel

In order to estimate the increase or growth in the product over a certain period of time, we will be using the Exponential GROWTH function.

We will select the range of known y values which is a number of bacteria growing with time and known x values which is the time period given in seconds and the new x values are 165 second for which we need to calculate the estimated increase in the number of bacteria.

The GROWTH formula in Excel that we will be using is:

Syntax: =ROUND(GROWTH(B2:B13,A2:A13,A14),0)

Result: 3880119807

GROWTH Function 2 - How to use GROWTH Function in Excel

The total estimated number of bacteria in the solution after 165 seconds will be approximately 3880119807

Note:

  1. #REF! error – Occurs if the [known_x’s] array is of a different length compared to the known_y’s array.
  2. #NUM! error – Occurs if any of the values in the known_y’s array is less than or equal to zero.
  3. #VALUE! error – This occurs if any of the values provided is non-numeric for known_y’s, [known_x’s] or [new_x’s] arrays.
  4. If we wish to see the GROWTH function visually, we can create a scatter chart of the data without displaying a line. The exponential option uses the function while the linear option uses the TREND function.
READ:  How to use FORECAST.ETS.CONFINT Function in Excel

 

Add a Comment

Your email address will not be published. Required fields are marked *