Rolling Forecast using SUMIFS and EOMONTH

CEpke

New Member
Joined
Jun 1, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.

1591052753697.png


1591052810513.png
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0

Forum statistics

Threads
1,214,983
Messages
6,122,588
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top