Search code examples
if-statementgoogle-sheetsfilterarray-formulasforecasting

How to use the Growth and Filter functions to make a dynamic range in Google Sheets based on ID and missing values?

I have a list of accounts featuring their product usage monthly within their current contract dates. I want to use the Google Sheets Growth function to look at where they are currently in their contract and where we expect them to land. Obviously, customers are at different stages in their contract and I want to be able to dynamically write a formula to analyze this.

A very close tutorial to what I think I need is located here, but this does not account for having multiple customer IDs in the same list:

https://infoinspired.com/google-docs/spreadsheet/dynamic-ranges-in-growth-trend-forecast-in-google-sheets/

A sample of the raw data can be seen here where I have manually entered growth formulas based on contract dates:

RAW DATA

Any help would be appreciated, I am simply not advanced enough with the FILTER function to work this one out!

Solution

  • delete E2:F range and paste this into E2 cell:

    =ARRAYFORMULA(IF(A2:A<>"", COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)), ))

    0

    paste this into F2 cell and drag down:

    =IF(D2<>"(null)",,GROWTH(
 INDIRECT(ADDRESS(MIN(FILTER(ROW(E:E), A:A=A2)), 4, 4)&":"&
          ADDRESS(MIN(FILTER(ROW(E:E), A:A=A2, D:D="(null)"))-1, 4, 4)),
 INDIRECT(ADDRESS(MIN(FILTER(ROW(E:E), A:A=A2)), 2, 4)&":"&
          ADDRESS(MIN(FILTER(ROW(E:E), A:A=A2, D:D="(null)"))-1, 2, 4)), B2))

    0