Analyzing historical data is crucial for key decision-making, and Google Sheets can assist you in this process. Its built-in functions can simplify trend analysis, and the GROWTH function is particularly useful for this purpose.
The GROWTH function in Google Sheets helps you analyze historical data from your business, which can be vital for making informed decisions. This function is most effective when data values increase or decrease at accelerating rates. For data that changes at a steady rate, you should use the TREND or FORECAST function, which applies the least squares method to predict future values.
Unlike the linear trend line produced by TREND, which is a straight line that best fits your data, the GROWTH function generates an exponential trend line that curves to fit data points.
Below, you’ll find an explanation of how to use the GROWTH function in Google Sheets. First, I’ll introduce the function’s syntax, followed by an example formula.
GROWTH Function: Syntax and Formula Examples
Syntax:
GROWTH(known_data_y, [known_data_x], [new_data_x], [b])I think the below screenshot can help you to better understand the GROWTH function Syntax (only the essential part).
In this example, I want to predict the sales values for the month of October, November, and December (B11, B12, and B13) by using the GROWTH function. Before going to that formula, see a brief description of the arguments used in this function.
known_data_y– The range containing the known dependent (y) values (here the sales values in the range B2:B10).known_data_x– The values of the independent variable(s) (here the months in the range A2:A10) corresponding withknown_data_y.new_data_x– The data points (here the months in the range A11:A13) to return the ‘y’ values.b– If TRUE or omitted, the GROWTH function calculates an exponential trend line that best fits your data. If FALSE, the function forces the exponential trend line to start at zero on the y-axis.
Google Sheets GROWTH Formula for Predicting Future Sales
The GROWTH formula below is based on the sample data shown in the image above:
=GROWTH(B2:B10, A2:A10, A11:A13)This formula predicts the sales values for October, November, and December.
In the screenshot below, I have entered the GROWTH, TREND, and FORECAST formulas side by side for easy comparison and understanding.
How can I use the GROWTH function to be a working formula as opposed to only being able to use it once? For example, I would like to write one growth formula and be able to enter future values in order to change the output of the formula. The issue is that I do not know how to make it work after a few days without changing the ranges within it. Is there a way around this?
Hi, Luke,
As per my above example, I mean the months in Column A and values in Column B, you can try the below formula.
=growth(filter(B2:B,B2:B<>""),filter(A2:A,B2:B<>""),filter(A2:A,(N(B2:B)=0)*(A2:A>0)))This covers infinite ranges in Growth in Google Sheets. Just try it and let me know. So that I can come up with the formula explanation.
Thanks.