Analyzing historical data of your business is important in key decision making and of course, Google Sheets can help you in this direction. Google Sheets can ease your job with its built-in functions that capable of doing trend analysis. No doubt you can use the GROWTH function in Google Sheets for trend analysis.
The GROWTH function in Google Sheets can help you to easily analyze historical data that you have sourced from your business which can play a vital role in your decision making.
The GROWTH function is most useful when data values rise/fall at increasingly higher rates. If the data values are increasing or decreasing at a steady rate, use the TREND or FORECAST function which uses the least squares method to forecast future values.
Unlike the linear trend line (TREND) which is a best fit straight line, an exponential trend (GROWTH) line is a curved one.
See how to use the GROWTH function in Google Sheets below. First, let me introduce to you the function syntax and then the formula example follows.
GROWTH Function in Google Sheets – 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 with known_data_y.
new_data_x – The data points (here the months in the range A11: A13) to return the ‘y’ values.
You can refer this source to know more about the syntax arguments used in the function GROWTH.
Google Sheets GROWTH Formula That Predicts Future Sales
The below Growth formula is based on the sample data which I have shown in the above image.
=growth(B2:B10,A2:A10,A11:A13)
This formula predicts the sales values for the month of October, November, and December. In the below screenshot I have entered the GROWTH, TREND, and FORECAST formulas side by side. So you can easily understand them.
That’s all about the GROWTH Function in Google Sheets.
Related: Dynamic Data Ranges in Growth, Trend, and Forecast in Google Sheets.
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.