Hi Guys, just wondering if any of you excel gurus can help?
I have a worksheet that forecasts spend, as can be seen in the screenshot:
Column D populates Months from another worksheet "=EDATE(B6, SEQUENCE(D3,1,0))"
Column E populates Spend from another worksheet "=IF(D9="","",SUMIFS(TRACK_InvValue,TRACK_InvDate,">="&D9,TRACK_InvDate,"<="&EOMONTH(D9,0)))"
Column G generates a forecast for months that don't yet have an actual value "=FORECAST.ETS.CONFINT(D26,$E$9:E25,$D$9:D25,Lists!$V$3,1,1)"
If there is a value in the spend column, the the formula shouldn't calculate a forecast figure.
If there is a $0 in the spend column, the formula should calculate a forecast from the spend figures available.
What I want to do is create a dynamic named range or have a dynamic formula that will change the range in the forecast formula that will automatically update as new actual figures appear in the spend Column E
I do hope this makes sense? As it stands the forecasting does work, however the user must manually adjust the forecast.ets.confint range every time a new spend figure appears in the spend column. Users of this template will have little experience with excel formulas so I don't want them trying to extend ranges and messing up their figures (the more automation and less user input the better).
Thanks Guys
Shot1.png
Bookmarks