The GROWTH function in Excel is used to predict future values based on existing data points by fitting an exponential growth trend. It is particularly useful for forecasting when you expect values to grow exponentially. Here’s a step-by-step guide on how to use the GROWTH function:
Syntax
=GROWTH(known_y's, [known_x's], [new_x's], [const])
- known_y’s: This is a required argument. These are the set of known y-values, the dependent data points.
- known_x’s: This is an optional argument. These are the set of known x-values, the independent data points. If omitted, Excel assumes these are {1, 2, 3, …}.
- new_x’s: This is an optional argument. These are the x-values for which you want to predict the y-values. If omitted, predictions are made at the known_x’s.
- const: This is an optional logical value. If TRUE or omitted, the constant “b” in the equation y = b*m^x is calculated normally. If FALSE, “b” is set equal to 1, forcing the model through the origin.
Steps to Use the GROWTH Function
=GROWTH(B2:B10, A2:A10)
- Prepare Your Data: Organize your data in two columns. For instance, one column for x-values (independent variable) and another for y-values (dependent variable).
- Select a Cell: Click on a cell where you want the prediction or results to appear.
- Enter the Formula:
- With known x and y values:
Here, B2:B10 are known_y’s and A2:A10 are known_x’s.
=GROWTH(B2:B10, A2:A10, A11:A15)
Here, A11:A15 are the new_x values where you want predictions.
=GROWTH(B2:B10, A2:A10, A11:A15, FALSE)
- Constant Option: If you wish to force the regression through the origin, set `const` to FALSE:
- Array Enter (if needed): If you want to fill a range with predicted values, select the entire range first, type the formula, and press `CTRL + SHIFT + ENTER` for array formulas (in older Excel versions).
Example
Suppose you have sales data over the years in two columns `Year` (A2:A10) and `Sales` (B2:B10). If you want to predict sales for the years in cells A11:A15, you use:
=GROWTH(B2:B10, A2:A10, A11:A15)
Tips
- Ensure that your x and y data are of the same length; otherwise, the function will return an error.
- Use GROWTH when you anticipate exponential growth. For linear trends, consider using the TREND function instead.
This method will provide you the ability to make informed forecasts based on past patterns, aiding decision-making in settings like sales predictions, budgeting, and more.
Unlock Your Potential
Excel
Access
Power BI
