I am building a forecasting model that will allow for multiple ways to trend actuals data forward and I am looking for a formula to help me do that automatically where I don't have to adjust the formula every month. Below is the example data. I will have 2 full years of actuals up to the current month in the first set of columns (I used 6 month years for this example to simplify). And then in the next set of columns you will see for FY19 the 2 months of actuals that have come in for Months 1 & 2 are reflected. In the row with "3 Month Avg" I want Months 3-6 to calculate the average of the last 3 months (so FY18 Month 6, FY19 Month 1 & FY19 Month 2). But I want to formula to know which 3 months to use based on the last month of Actuals we have (so in this case FY19 Month 2) so that when Actuals for Month 3 come in it will automatically change the go forward formula to be an avg of FY19 Months 1-3 for the remaining Months of the year. I also want to do the same thing for a 6 month avg. Ideally it would be in the same formula with an IF statement so I can use a drop-down and choose which method I want. Hopefully this makes sense and someone out there will be able to help! Thanks in advance!
<colgroup><col span="7"><col><col span="7"><col><col span="7"></colgroup><tbody>
</tbody>
| Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Actuals | Forecast | Forecast | Forecast | Forecast | ||
| FY18 | FY18 | FY18 | FY18 | FY18 | FY18 | FY18 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | FY19 | ||
| Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Total | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Total Yr | Drop-down | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | Month 6 | Total Yr | |
| 171 | 146 | 178 | 171 | 146 | 178 | 494 | 55 | 146 | 371 | 3 Month Avg | 55 | 146 | ||||||||||
| 6 Month Avg | 55 | 146 |
<colgroup><col span="7"><col><col span="7"><col><col span="7"></colgroup><tbody>
</tbody>
