30 day Moving Average

[Gusher]

Hi

In Col A I have dates but Saturday and Sunday are excluded i.e only weekdays. I have date for several years.

In Col B I have prices.

In col C I wish to calculate the 30 day moving price average WITH weekend included in the 30 day count.

Any suggestions please?

Best Regards

Mark

 

[Saba Sabaratnam]

Hi Mark,

My solution to your problem needs some helper columns.


I am calculating avg for 7 days, you can change it to 30 wherever you see in my formula.

Enter the following formula in C1 and copy it down

=1+IF(WEEKDAY(A1)=6,2,0)

Enter the following formula in D1 and copy it down

=CEILING(SUM($C$1:C1),7)

Finally enter the following formula in E1 and copy it down

=IF(IF((MOD(SUM($C$1:C1),7))=0,SUM($C$1:C1),"")<>"",SUMIFS(B:B,D:D,D1)/7,"")

Kind regards

Saba

 

[Snakehips]

@Gusher
Am I correct in thinking that in calculating your 30 day average you are wanting the non-recorded Saturdays and Sundays to be considered to be holding the value of the previous Friday?

If so then try the following formula in column C.
Enter it at row 22 and drag down as required. In rows 1-21 it will return an error.

=(SUMIF(A1:A22,">="&A22-29,B1:B22)+SUMPRODUCT((WEEKDAY(A2:A22,2)=1)*(B1:B21)*2)-IF(WEEKDAY(A22,2)=1,WEEKDAY(A22,2)*B1,0))/30

Hope that helps

 

[Gusher]

Hi Mark,

My solution to your problem needs some helper columns.


I am calculating avg for 7 days, you can change it to 30 wherever you see in my formula.

Enter the following formula in C1 and copy it down

=1+IF(WEEKDAY(A1)=6,2,0)

Enter the following formula in D1 and copy it down

=CEILING(SUM($C$1:C1),7)

Finally enter the following formula in E1 and copy it down

=IF(IF((MOD(SUM($C$1:C1),7))=0,SUM($C$1:C1),"")<>"",SUMIFS(B:B,D:D,D1)/7,"")

View attachment 31917

Kind regards

Saba

Many Thanks Saba, that worked.

 

[Gusher]

@Gusher
Am I correct in thinking that in calculating your 30 day average you are wanting the non-recorded Saturdays and Sundays to be considered to be holding the value of the previous Friday?

If so then try the following formula in column C.
Enter it at row 22 and drag down as required. In rows 1-21 it will return an error.

=(SUMIF(A1:A22,">="&A22-29,B1:B22)+SUMPRODUCT((WEEKDAY(A2:A22,2)=1)*(B1:B21)*2)-IF(WEEKDAY(A22,2)=1,WEEKDAY(A22,2)*B1,0))/30

Hope that helps

Many thanks Sankehips