Recently one of my readers asked me about the possibility of using dynamic ranges in Growth function in Google Sheets. With the help of using the Filter function, we can make it possible not only in the Growth function but also in Trend and Forecast.

Don’t let the above function names confuse you. Learn them using my Google Sheets functions guide which covers several Google Sheets functions and its use.

This post is about the possibility of using infinite known_data_x, known_data_y, and new_data_x in the exponential growth (GROWTH), linear trend (TREND), and in the linear regression (FORECAST) calculations in Google Sheets.

Normally you can not use infinite ranges like A2: A, B2: B in these functions. But you can use infinite/dynamic data ranges in Growth, Trend, and Forecast functions with the help of the Filter function in Google Sheets.

Understand Dynamic Data Ranges in Growth, Trend, and Forecast in Google Sheets

In the below screenshot I have highlighted the data_x, data_y, and new_data_x.

Take a look at the legends and the dataset. The color coding is given to make you understand what is data_x, data_y, and the new_data_x in Growth, Trend, and Forecast functions.

There are existing values in the range data_x and data_y. So you can call it or sometimes I may call it known_data_x and known_data-y. We have to find the values against new_data_x.

First, understand what is fixed data range in the above said three functions.

Exponential Growth:

The Syntax of the Growth Function:

GROWTH(known_data_y, [known_data_x], [new_data_x], [b])

=GROWTH(B2:B10,A2:A10,A11:A13)

Linear Trend:

The Syntax of the TREND Function:

TREND(known_data_y, [known_data_x], [new_data_x], [b])

=TREND(B2:B10,A2:A10,A11:A13)

Linear Regression:

The Syntax of the FORECAST Function:

ArrayFormula(FORECAST(x, data_y, data_x))

=ArrayFormula(FORECAST(A11:A13,B2:B10,A2:A10))

I have used fixed data ranges in the above three formulas. Now time to move to the use of dynamic data ranges in Growth, Trend, and Forecast functions in Google Sheets.

Suppose my above data range is an output of other formula/formulas or updated manually by users. In such cases, the data ranges may get changed frequently. That makes a problem in the above formulas. Since the range is fixed the formula may not include the values in the new cells.

For example in the earlier formulas, the data ranges in use are as follows.

data_x: A2: A10

data_y: B2: B10

new_data_x: A11: A13

Now see the same dataset but added new values in data_x, data_y, and new_data_x columns.

Now in the new dataset, the ranges are as follows.

data_x: A2: A13

data_y: B2: B13

new_data_x: A14: A15

Can I accommodate this new changes, without changing the formula?

Yes! Let me explain to you how to use dynamic Data Ranges in Growth, Trend, and Forecast in Google Sheets. Let me begin with the Growth Function.

Expanding/Dynamic/Infinite Ranges in Growth Function in Google Sheets

As I have mentioned the expanding range in Growth means cover infinite data_x, data_y, and new_data_x values. For example,

See the normal Growth formula first.

=GROWTH(B2:B10,A2:A10,A11:A13)

Here is the dynamic range in Growth.

=GROWTH(filter(B2:B,B2:B<>""),filter(A2:A,B2:B<>""),filter(A2:A,(N(B2:B)=0)*(A2:A>0)))

See how the formula predicts the growth dynamically.

Formula Explanation

I am trying to explain the formula by following the Growth function Syntax.

GROWTH(known_data_y, known_data_x, new_data_x)

Generic Formula Using Filter Inside Growth:

=growth(filter(known_data_y,known_data_y<>""),filter(known_data_x,known_data_y<>""),filter(known_data_x,(N(known_data_y)=0)*(known_data_x>0)))

That means;

known_data_y: Filter B2: B if B2: B is not equal to blank.

known_data_x: Filter A2: A if B2: B is not equal to blank.

new_data_x: Filter A2: A if B2: B is equal to zero as well as A2: A is greater than 0.

Hope from this explanation you will get into the logic.

In the case of new_data_x, you may have some doubt about the Filter formula in use. In that, I have used an OR logic in Filter. Follow this tutorial to know the use of Filter with OR – How to Use AND, OR with Google Sheets Filter Function.

This way you can use infinite/dynamic data ranges in Growth function in Google Sheets.

Dynamic Range in TREND Function in Google Sheets

The dynamic range in functions like TREND, GROWTH, and FORECAST revolves around the use of Filter in data ranges.

Here in TREND also you can follow the GROWTH function approach. See how to use dynamic range in TREND in Google Sheets.

Formula:

=TREND(filter(B2:B,B2:B<>""),filter(A2:A,B2:B<>""),filter(A2:A,(N(B2:B)=0)*(A2:A>0)))

Syntax:

TREND(known_data_y, known_data_x, new_data_x)

Here, only the Function name changes. So check the dynamic range in Growth example to understand this formula better.

Dynamic Range in FORECAST Function in Google Doc Sheets

Here also the formula is almost the same except the position of arguments. Additionally, I have used the ArrayFormula since the FORECAST is not an array formula by default.

Formula:

=ArrayFormula(FORECAST(filter(A2:A,(N(B2:B)=0)*(A2:A>0)),filter(B2:B,B2:B<>""),filter(A2:A,B2:B<>"")))

Syntax:

ArrayFormula(FORECAST(x, data_y, data_x))

Conclusion

If your data range, I mean the known_data_x and known_data_y are kept increasing, and also there are changes in the new_data_x (of course there will be) then you can follow the above approach.

The secret of dynamic Data Ranges in Growth, Trend, and Forecast functions in Google Sheets depends on how you use Filter.

If you know Filter, then you can easily conquer how to use infinite ranges aka dynamic ranges in the above-said functions. Thanks for the stay. Enjoy!