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

Published on

Recently, one of my readers asked about the possibility of using dynamic ranges in the GROWTH function in Google Sheets. With the help of the FILTER function, you can apply dynamic ranges not only in the GROWTH function but also in the TREND and FORECAST functions.

Don’t be puzzled by the function names. My Google Sheets functions guide provides detailed explanations of these functions and their applications. Here’s a quick overview of their purposes:

  • FORECAST: Used to predict future values based on historical data. It’s commonly applied to estimate a future value using past data points.
  • TREND: Fits a linear trend line to your data and extends it beyond the existing values. This function helps forecast a range of future values based on a linear pattern.
  • GROWTH: Projects future values by fitting an exponential curve to your existing data. Ideal for data that shows exponential growth or decay.

This post explores how to use dynamic ranges for known_data_x, known_data_y, and new_data_x in exponential growth (GROWTH), linear trend (TREND), and linear regression (FORECAST) calculations in Google Sheets.

Typically, you cannot use infinite ranges like A2:A or B2:B directly in these functions. However, you can leverage the FILTER function to work with dynamic data ranges in GROWTH, TREND, and FORECAST functions.

Understanding the Concept

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

Linear trend with data_x, data_y, and new_data_x values

Refer to the legends and dataset for color coding, which helps identify data_x, data_y, and new_data_x in the GROWTH, TREND, and FORECAST functions.

The ranges for data_x and data_y contain existing values. Sometimes, these might be referred to as known_data_x and known_data_y. We need to find values corresponding to new_data_x.

First, let’s understand the fixed data ranges used in the three functions:

Exponential Growth:

Syntax of the GROWTH Function: GROWTH(known_data_y, [known_data_x], [new_data_x], [b])

Formula:

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

Linear Trend:

Syntax of the TREND Function: TREND(known_data_y, [known_data_x], [new_data_x], [b])

Formula:

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

Linear Regression:

Syntax of the FORECAST Function: ArrayFormula(FORECAST(x, data_y, data_x))

Formula:

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

In these examples, I’ve used fixed (closed) data ranges. Now, let’s explore how to use dynamic data ranges in the GROWTH, TREND, and FORECAST functions in Google Sheets.

Suppose the data ranges are updated either by other formulas or manually by users. In such cases, the ranges may change frequently, causing issues with fixed ranges as they might not include new values.

For instance, with the initial formulas, the data ranges are:

  • data_x: A2:A10
  • data_y: B2:B10
  • new_data_x: A11:A13

If new values are added, the updated ranges might be:

  • data_x: A2:A13
  • data_y: B2:B13
  • new_data_x: A14:A15
Infinite range used in Growth, Trend, and Forecast functions

Can we accommodate these changes without altering the formula?

Yes! Let me explain how to use dynamic data ranges in the GROWTH, TREND, and FORECAST functions in Google Sheets, starting with the GROWTH function.

Using Dynamic Ranges with the GROWTH Function in Google Sheets

As mentioned, using an expanding range in the GROWTH function allows you to handle dynamic or infinite data for data_x, data_y, and new_data_x. For example:

Standard GROWTH Formula:

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

Dynamic Range Formula:

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

In the dynamic range formula, FILTER functions adjust the ranges to include all relevant data points, enabling the GROWTH function to predict values based on the most current data.

Example of dynamic data ranges in Growth, Trend, and Forecast functions

Formula Explanation

Let me break down the formula according to the GROWTH function’s syntax:

GROWTH(
   known_data_y, 
   known_data_x, 
   new_data_x
)

Generic Formula Using FILTER within 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))
)

Here’s what each part means:

  • known_data_y: Filters the range B2:B to exclude blanks (B2:B<>"").
  • known_data_x: Filters the range A2:A, ensuring corresponding B2:B values are not blank (B2:B<>"").
  • new_data_x: Filters A2:A where B2:B is equal to zero and A2:A is greater than 0 (N(B2:B)=0 and A2:A>0).

This logic helps dynamically adjust the data ranges.

Regarding the new_data_x filter, you might wonder about the logic used. The formula employs OR logic in the FILTER function, which is explained in more detail in my tutorial: How to Use AND, OR with Google Sheets Filter Function.

Using this method, you can apply dynamic or infinite data ranges in the GROWTH function within Google Sheets.

Dynamic Range in the TREND Function

For the TREND function, you can follow a similar approach as with the GROWTH function. Here’s how to apply a dynamic range in the TREND function 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
)

In this formula, only the function name changes. For a better understanding of how this works, refer to the dynamic range example for the GROWTH function.

Dynamic Range in the FORECAST Function

The approach for creating a dynamic range in the FORECAST function is similar to TREND and GROWTH, with a few differences.

In this case, the arguments are positioned differently, and since the FORECAST function isn’t an array formula by default, I’ve used the ArrayFormula function.

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—both known_data_x and known_data_y—continues to grow, and there are updates to new_data_x (which is likely), you can apply the approach outlined above.

The key to using dynamic data ranges in the GROWTH, TREND, and FORECAST functions in Google Sheets lies in how you utilize the FILTER function.

Once you understand FILTER, you’ll easily master how to use dynamic or infinite ranges in these functions. Thanks for reading, and happy learning!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.