Rolling Forecast using SUMIFS and EOMONTH

[CEpke]

Want to use a rolling average of last 3 months to forecast future 12 months. Forecast is in one tab, history in another tab. I have a formula using SUMIFS and EOMONTH, -1, EOMONTH, -2, etc. For some months the forecast will pull from only history, in other months the forecast will pull a combination of history and forecasted data and in some months the forecast will be pulling from the last 3 months forecasted. Formula seems to work, but not very efficient. Would like to find a more streamlined way to write this formula, because I am thinking about changing to last 12 months rolling average and my EOMONTH, -1, will be really clunky going all the way to -12. Sample below. Thank you for offering assistance.

 

[jasonb75]

You can set a date range by using

=SUMIFS(Actual!$6:$6,Actual!$4:$4,">="&EOMONTH(B4,-3),Actual!$4:$4,"<="&EOMONTH(B4,-1))/3

Or the principle of the formula,

=SUMIFS(values , dates , ">="& earliest date , dates , "<="& latest date)

Personally, I would consider having the number of months in a cell to make it more flexible, e.g. with 3 in A5,

=SUMIFS(Actual!$6:$6,Actual!$4:$4,">="&EOMONTH(B4,-$A5),Actual!$4:$4,"<="&EOMONTH(B4,-1))/$A5

 

[CEpke]

Thank you! This worked perfectly.