Let’s say you’ve introduced new products or services to your business that have started doing very well. Not only do your revenue figures increase every month, but the “increase” itself is also growing. If you want to predict your future expenses, looking at the figures for previous years, you might notice that they’ve started growing exponentially as well.

Whichever the case, you can use Excel’s GROWTH function to obtain an estimate for the next month or year. If your figures show linear growth, you should use the TREND function instead.

In this article, you will learn what the GROWTH function in Excel is and how to use it to predict future values based on existing ones. In addition to the known values for the dependent variable, you can also include values for one or more independent variables.

What is Excel’s GROWTH Function?

The GROWTH function predicts future values based on the assumption of exponential growth.

To calculate this, the Excel GROWTH function requires values you already have for the dependent variable (known_y’s).

As for the optional values, these include those corresponding to the independent variable which you may or may not have (known_x’s), and those for which you’d like to find new values (new_x’s). The last is a logical value to determine the constant (const).

If your values show linear growth, you can use the TREND function to estimate future values.

GROWTH Function Syntax

The syntax for Excel’s GROWTH function is shown below:

• known_y’s (required): These are the values you already have for the dependent variable. If any of the values in this array is 0 or negative, you will get the “#NUM!” value error.
• known_x’s (optional): An optional set of values for the independent variable, which you may already have. If you don’t, you can omit this parameter.
• new_x’s (optional): The new values of the independent variable (x), for which you want to find the values of the dependent variable (y). If this parameter is omitted, it is assumed to be the same as the known_x’s. If both known_x’s and new_x’s are omitted, they are assumed to be the array {1,2,3,…}.
• const (optional): A logical value that determines whether or not the constant (b) will be calculated:
  • If TRUE or omitted, b will be calculated.
  • If FALSE, b will be equal to 1. The formula is then adjusted to y = m^x.

To learn more about using this function with multiple variables in the form of complex arrays and vectors, visit Microsoft’s support page on this function.

How to Use Excel’s GROWTH Function?

The GROWTH function can return a value or an array of values. This is how you can use the GROWTH function in your worksheet to predict a single value and to predict an array of values.

Use GROWTH to Predict a Value

In the spreadsheet shown below, we have the values for monthly revenue for the last eleven months. I will use the GROWTH function to estimate the expected revenue for the next month, assuming exponential growth.

One column contains the values for the monthly revenue, known_y’s, and a second column contains the values for the month number known_x’s.

1. Click on the cell where you want the predicted value for revenue to appear. Here, B13.

Excel GROWTH Function: How to Use & Examples – Select Cell for New Y Value

2. Type in the formula (see Growth Function Syntax above) and select the known_y’s, that is all cells containing the revenue values. Then, add a semicolon to trigger the next argument. Here, “B2:B12;”.

Excel GROWTH Function: How to Use & Examples – Select Known Y Values

3. After the semicolon, include the range for known_x’s and add a semicolon before entering the next argument.

Excel GROWTH Function: How to Use & Examples – Select Known X Values

4. Select the cell with the new_x’s (here, “A13″) and add a semicolon to trigger the options for the const argument.

Excel GROWTH Function: How to Use & Examples – Select New X Value

5. The values for const are “FALSE – b is set equal to 1″ or “TRUE – b is calculated normally”. If you choose “TRUE” or leave it empty, the value for “b” is calculated. If you choose “FALSE”, it will be set equal to 1.

Excel GROWTH Function: How to Use & Examples – Select Const Value

6. Finish the formula by adding the closing parenthesis and press “Enter”.

Excel GROWTH Function: How to Use & Examples – Press Enter

7. Excel will now show the predicted value for the last month in the cell you initially selected.

Excel GROWTH Function: How to Use & Examples – Predicted Value

Use GROWTH to Predict an Array of Values

Let’s say your spreadsheet contains values for yearly expenses over the last six years. Use the GROWTH function to estimate the expenses for the next three years, assuming exponential growth.

In the first column, you can include the years, known_x’s, and the second column should contain the values for the yearly expenses, known_y’s. To predict three new values for known_y’s, you’ll need to use the function as an array formula.

1. Select the cells where you want the new values for expenses, new_x’s, to show.

Excel GROWTH Function: How to Use & Examples – Select Cells for New Array

2. Click on the formula bar and type in the GROWTH function. Select the known_y’s and add a semicolon to trigger the next argument.

Excel GROWTH Function: How to Use & Examples – Select Known Y Values

3. Select the known_x’s and add a semicolon to trigger the next argument.

Excel GROWTH Function: How to Use & Examples – Select Known X Values

4. Select the new_x’s and add a semicolon to see the options for const.

Excel GROWTH Function: How to Use & Examples – Select New X Values

5. The values for const are “FALSE – b is set equal to 1″ or “TRUE – b is calculated normally”. If you choose “TRUE” or leave it empty, the value for “b” is calculated. If you choose “FALSE”, it will be set equal to 1.

Excel GROWTH Function: How to Use & Examples – Select Const Value

6. After adding the final parenthesis, remember to press Ctrl + Shift + Enter for the array of values to be returned correctly.

Excel GROWTH Function: How to Use & Examples – Press Ctrl + Shift + Enter

7. Excel should now return the predicted values for the next three years in the corresponding cells.

Excel GROWTH Function: How to Use & Examples – Predicted Values

Conclusion

As you have seen, Excel’s GROWTH function is useful if you need a future estimate and the values you already have show exponential growth. For linear growth, the TREND function is more appropriate.

You now know how to use the GROWTH function to predict both a single future value and an array of values based on known values for two variables. In the first example, the function was used to calculate the predicted value for revenue in the next month. In the second, the function was used to calculate the predicted expenses for the next three years.