Forecast Trend formula needed

[ShanaVT]

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!

[TABLE="width: 1456"]
<colgroup><col span="7"><col><col span="7"><col><col span="7"></colgroup><tbody>[TR]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD][/TD]
[TD][/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Forecast[/TD]
[TD]Forecast[/TD]
[TD]Forecast[/TD]
[TD]Forecast[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]FY18[/TD]
[TD]FY18[/TD]
[TD]FY18[/TD]
[TD]FY18[/TD]
[TD]FY18[/TD]
[TD]FY18[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD][/TD]
[TD][/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[TD]FY19[/TD]
[/TR]
[TR]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[TD]Month 4[/TD]
[TD]Month 5[/TD]
[TD]Month 6[/TD]
[TD]Total[/TD]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[TD]Month 4[/TD]
[TD]Month 5[/TD]
[TD]Month 6[/TD]
[TD]Total Yr[/TD]
[TD][/TD]
[TD]Drop-down[/TD]
[TD]Month 1[/TD]
[TD]Month 2[/TD]
[TD]Month 3[/TD]
[TD]Month 4[/TD]
[TD]Month 5[/TD]
[TD]Month 6[/TD]
[TD]Total Yr[/TD]
[/TR]
[TR]
[TD] 171[/TD]
[TD] 146[/TD]
[TD] 178[/TD]
[TD] 171[/TD]
[TD] 146[/TD]
[TD] 178[/TD]
[TD] 494[/TD]
[TD] 55[/TD]
[TD] 146[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 371[/TD]
[TD][/TD]
[TD]3 Month Avg[/TD]
[TD] 55[/TD]
[TD] 146[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6 Month Avg[/TD]
[TD] 55[/TD]
[TD] 146[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[cunningAce]

This array formula may help,
Although the 'Total' for the year end, throws the average out.


[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
3	Actuals	Actuals	Actuals	Actuals	Actuals	Actuals	Actuals	Actuals	Actuals	Actuals	Actuals	Actuals
4	FY18	FY18	FY18	FY18	FY18	FY18	FY19	FY19	FY19	FY19	FY19	FY19
5	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6
6	171	146	178	171	146	178	55	146
7
8	3	126
9	6	146
Sheet1

Cell Formulas
Range	Formula
C8	{=SUM(IF(COLUMN(B6:M6)>=LARGE(IF(B6:M6>0,COLUMN(B6:M6)),$B$8),B6:M6))/$B$8}
C9	{=SUM(IF(COLUMN(B6:M6)>=LARGE(IF(B6:M6>0,COLUMN(B6:M6)),$B$9),B6:M6))/$B$9}
Press CTRL+SHIFT+ENTER to enter array formulas.

[/FONT]