Dynamic moving average calculation

[jaimonsk]

Hello Experts,

My source data looks something like this. Here the divisor would be changing like 2,3,4,5...

Date	Close	Divisor
21-12-2021	172.990005	2
22-12-2021	175.639999	2
23-12-2021	176.279999	2
27-12-2021	180.330002	2
28-12-2021	179.289993	2
29-12-2021	179.380005	2
30-12-2021	178.199997	2
31-12-2021	177.570007	2
03-01-2022	182.009995	2
04-01-2022	179.699997	2

Based on that we would need to create a new column called moving average in such a way that if the divisor =2 then Moving Avg would be like rows  1, 1+2, 2+3, 3+4 

ex:

Date	Close	Moving Average	Divisor
21-12-2021	172.990005	172.990005	2
22-12-2021	175.639999	260.8100045	2
23-12-2021	176.279999	263.7799985	2
27-12-2021	180.330002	266.445	2
28-12-2021	179.289993	269.9749985	2
29-12-2021	179.380005	268.9799955	2
30-12-2021	178.199997	268.4800035	2
31-12-2021	177.570007	266.9850005	2
03-01-2022	182.009995	268.5750045	2
04-01-2022	179.699997	271.8599935	2

we would need to create a dynamic solution of moving average based on divisor. Can someone share an idea?

[mmenth]

Hi @jaimonsk,

I'm not sure how you got those moving number averages, but going by your other instructions I would suggest using a batch macro like the attached. It basically goes through each date in a loop and uses sorting, filtering, and summarizing to isolate the records used to do a moving sum/average.

Hope this helps!

Best,

mmenth

[Qiu]

@jaimonsk 
Can you confirm your moving average?

It doest look like what you described. 😁

[Jishnu_K · Accepted solution]

Hi Jaimon,

I have created a Batch macro for this logic. Please find the attachment.