Update Real Time Forecast upper and lower confidence bound

[jonastiger]

Hi
I´m using forecast sheet sometimes to some ponctual tasks.
Now I have a table that is updated everyweek, but I can´t update forecast because range values (col B) are dynamic.

TIMELINE	VALUES	FORECAST	LOWER CONF BOUND	UPPER CONF BOUND
17/03/2023	619
24/03/2023	774
31/03/2023	828	828	828	828
07/04/2023		901	861	940
17/04/2023		973	912	1034
21/04/2023		1045	955	1241

C5=FORECAST.ETS(A5,$B$2:$B$4,$A$2:$A$4,1,1,1)
D5=B5-FORECAST.ETS.CONFINT(A5,$B$2:$B$4,$A$2:$A$4,0.95,1,1,1)
E5=B5+FORECAST.ETS.CONFINT(A5,$B$2:$B$4,$A$2:$A$4,0.95,1,1,1)

Now, if I update tha table and B5 fills with 930, for example, the locked ranges in formulas shoud be updated to $B$2:$B$5,$A$2:$A$5.
I tried dynamic ranges such as $B$2:$B5,$A$2:$A5, but it seems not work.

Is there a way to update forecast bypassing the lock the range values and not forcing a new forecast sheet everyweek?

Thank you in advance

 

[Peter Bartholomew]

If you use Excel 365 it is possible to set the forecast columns up to be a single dynamic array,

where the Lambda function
= Forecastλ(timeline, valueColumn)
is defined by

= LET(
    actualsCount,   XMATCH(TRUE, ISNUMBER(valueColumn), 0, -1),
    actualsPeriod,  TAKE(timeline, actualsCount),
    forecastPeriod, DROP(timeline, actualsCount),
    actualsValues,  TAKE(valueColumn, actualsCount),
    forecastValue,  FORECAST.ETS(forecastPeriod, actualsValues, actualsPeriod, 1, 1, 1),
    confidenceInt,  FORECAST.ETS.CONFINT(forecastPeriod, actualsValues, actualsPeriod, 95%, 1, 1, 1),
    lowerCBound,    forecastValue - confidenceInt,
    upperCBound,    forecastValue + confidenceInt,
    actuals,        CHOOSECOLS(actualsValues, 1, 1, 1),
    forecast,       HSTACK(forecastValue, lowerCBound, upperCBound),
    VSTACK(actuals, forecast)
  )

It reads the value column to identify the last actuals period and then evaluates the corresponding time periods, both actual and forecast. Standard formulas provide the forecast and the confidence interval. These are combined and stacked to give the entire forecast array starting at 17/03/2023.