Moving average

[gravanoc]

I'm trying to create a formula that shows the remaining forecast values for a certain time period based on how the actual values are inputted each day.

In my setup I have approx. a full calendar year in column C11:C358, the forecast is in E6, the sum of the actuals in F11:F353 (where they fall between the correct dates) is in F6, the start date is in E7, and the end date is in E8.
An example: Forecast value is 15,288, start date is 1/25/2022, and end date is 3/15/2022. That's a 49 day time period, but I add +1 to make it inclusive, so it's 50. In E11:E358 I have a formula that checks if the adjacent dates are within the start & end date, and if so, I divide the forecast by the days. That is, 15,288 / 50 = 306.75.

The next part is where I'm having trouble, since if I change the logic so that the actuals are subtracted from the forecast and their difference is divided by the days, then it will adjust the remaining days downward. For example, if I input 306 as an actual value next to the first forecast value in the column, then the remaining values will average to 300. Unfortunately, it will always decrease the average if the value is positive. If I enter a number less than one of the forecast values then the remaining forecast values should increase.

Here is a screenshot to help visualize. XL2BB below.

PMV Rebuild v5.xlsm
	C	D	E	F
2		Tracker Pile [Forecast QTY: 145,284 Actual QTY: 306]
3		Subproject	S1
4		MWac	50
5		Q/MWac	306
6		Forecast / Actual Qty	15,288	306
7		Start	1/25/2022
8		Finish	3/15/2022
9
10	Dates	Running Total	Forecast Remaining	Actual Values
11	Thursday, January 20, 2022	0
12	Friday, January 21, 2022	0
13	Saturday, January 22, 2022	0
14	Sunday, January 23, 2022	0
15	Monday, January 24, 2022	0
16	Tuesday, January 25, 2022	306	0	306
17	Wednesday, January 26, 2022	606	300
18	Thursday, January 27, 2022	905	300
19	Friday, January 28, 2022	1,205	300
20	Saturday, January 29, 2022	1,505	300
21	Sunday, January 30, 2022	1,804	300
Formula

Cell Formulas
Range	Formula
D2	D2	=Sheet1!B5 & " [Forecast QTY: " & TEXT(SUM(E6,G6,I6,K6,M6),"#,##0") & " Actual QTY: " & TEXT(SUM(F6,H6,J6,L6,N6),"#,##0") & "]"
E5	E5	=E6/E4
E6	E6	=Sheet1!D5
F6	F6	=SUMIFS(F$11:F$358,$C$11:$C$358,">="&E$7,$C$11:$C$358,"<="&E$8)
D11:D21	D11	=IF(SUM(E11:M11)=0,,ROUND(SUM(E$11:M11),2))
E11:E21	E11	=IF(OR(F11=0,F11=""),IF(AND($C11<=E$8,$C11>=E$7),SUM((E$6-F$6)/SUM(E$8-E$7+1)),""),0)
C12:C21	C12	=C11+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C11:C358	Expression	=$C11=CODdate	text	NO
C11:C358	Expression	=$C11=IADdate	text	NO
C11:C358	Expression	=COUNTIF(Holidays,#REF!)	text	NO
C11:C358	Expression	=OR(WEEKDAY(C11)=1,WEEKDAY(C11)=7)	text	NO
C11:C358	Expression	=OR(WEEKDAY(#REF!)>1,WEEKDAY(#REF!)<7)	text	YES

 

[SanjayGMusafir]

Check if this is what you were looking for -

It took me quite some time to understand what you probably wanted and where things were going wrong. Still until you verify everything is an assumption for me.

Also note, I have changed dates to numbers to create uniformity because dates in your part of the world and in my are are presented differently.

All Records.xlsb
	C	D	E	F
2		[Forecast QTY: 15,288 Actual QTY: 306]
3		Subproject	S1
4		MWac	50
5		Q/MWac	305.76
6		Forecast / Actual Qty	15288	306
7		Start	44,586
8		Finish	44,635
9
10	Dates	Running Total	Forecast Remaining	Actual Values
11	44,581	0
12	44,582	0
13	44,583	0
14	44,584	0
15	44,585	0
16	44,586	306	0	306
17	44,587	611.76	305.76
18	44,588	923.89	312.13
19	44,589	1242.66	318.77
20	44,590	1568.36	325.7
21	44,591	1901.29	332.93
Sheet1

Cell Formulas
Range	Formula
D2	D2	=Sheet1!B5 & " [Forecast QTY: " & TEXT(SUM(E6,G6,I6,K6,M6),"#,##0") & " Actual QTY: " & TEXT(SUM(F6,H6,J6,L6,N6),"#,##0") & "]"
E5	E5	=E6/E4
F6	F6	=SUMIFS(F$11:F$358,$C$11:$C$358,">="&E$7,$C$11:$C$358,"<="&E$8)
D11:D21	D11	=IF(SUM(E11:M11)=0,,ROUND(SUM(E$11:M11),2))
E11:E21	E11	=IF(OR(F11=0,F11=""),IF(AND($C11<=$E$8,$C11>=$E$7),ROUND(($E$6-$F$6)/SUM($E$8-C11+1),2),""),0)
C12:C21	C12	=C11+1

 

[gravanoc]

Thank you, that does work. Apologies for the late reply.

 

[SanjayGMusafir]

Thank you, that does work. Apologies for the late reply.

Glad to help you.