• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

moving average

dsb1985

New Member
Dear Members,

i want to apply a formula on attach sheet to get 3DMA(7 day moving average) & 7DMA(7 day moving average).
problem is highlighed cells(A2:A6) repeat because the stock data is for the period 08Jan to 21Jan.

Regards,
Deepak
 

Attachments

Senior Momentum

New Member
Convert that to a table for ease of reference. For the 3DMA the formula would be something like:

=SUM(([NO_OF_SHRS])*([SC_CODE]=[@[SC_CODE]])*([DATE]>=[@DATE]-3)*([DATE]<=[@DATE]))/SUM(1*([SC_CODE]=[@[SC_CODE]])*([DATE]>=[@DATE]-3)*([DATE]<=[@DATE]))

There may be a simpler way.
 

dsb1985

New Member
could not get the above formula, please see the below table.

SC_CODESC_NAMECLOSE PRICEDATE3DMA7DMA
500002ABB
1313.05​
08/01/2021​
500003AEGIS
258.25​
08/01/2021​
500008AMARA
1002.1​
08/01/2021​
500009A.SARA
23.8​
08/01/2021​
500010HDFC
2653.4​
08/01/2021​
500002ABB
1296.9​
11/01/2021​
500003AEGIS
261.35​
11/01/2021​
500008AMARA
993.9​
11/01/2021​
500009A.SARA
22.9​
11/01/2021​
500010HDFC
2751.65​
11/01/2021​
500002ABB
1270.3​
12/01/2021​
500003AEGIS
259.2​
12/01/2021​
500008AMARA
995.2​
12/01/2021​
500009A.SARA
22.7​
12/01/2021​
500010HDFC
2746​
12/01/2021​
500002ABB
1279.8​
13/01/2021​
500003AEGIS
277.85​
13/01/2021​
500008AMARA
986.75​
13/01/2021​
500009A.SARA
22.4​
13/01/2021​
500010HDFC
2670.25​
13/01/2021​
500002ABB
1276.05​
14/01/2021​
500003AEGIS
282.9​
14/01/2021​
500008AMARA
1013.75​
14/01/2021​
500009A.SARA
22.3​
14/01/2021​
500010HDFC
2684.3​
14/01/2021​
500002ABB
1282.5​
15/01/2021​
500003AEGIS
275.5​
15/01/2021​
500008AMARA
1006.7​
15/01/2021​
500009A.SARA
23.4​
15/01/2021​
500010HDFC
2631.95​
15/01/2021​
500002ABB
1344.95​
18/01/2021​
500003AEGIS
271.7​
18/01/2021​
500008AMARA
970.9​
18/01/2021​
500009A.SARA
22.95​
18/01/2021​
500010HDFC
2566.4​
18/01/2021​
500002ABB
1391.35​
19/01/2021​
500003AEGIS
270.75​
19/01/2021​
500008AMARA
993.4​
19/01/2021​
500009A.SARA
22.8​
19/01/2021​
500010HDFC
2654.9​
19/01/2021​
500002ABB
1393.2​
20/01/2021​
500003AEGIS
277.3​
20/01/2021​
500008AMARA
978.65​
20/01/2021​
500009A.SARA
22.75​
20/01/2021​
500010HDFC
2688.75​
20/01/2021​
500002ABB
1376.95​
21/01/2021​
500003AEGIS
276.15​
21/01/2021​
500008AMARA
973.3​
21/01/2021​
500009A.SARA
22.5​
21/01/2021​
500010HDFC
2662.75​
21/01/2021​
 

bosco_yip

Excel Ninja
Maybe,

1] "3DMA" in F2, formula copied down :

=IFERROR(AVERAGE(INDEX(D$2:D2,N(IF(1,AGGREGATE(14,6,ROW(D$2:D2)-ROW(D$1)/(A$2:A2=A2),{1;2;3}))))),"")

2] "7DMA" in G2, formula copied down :

=IFERROR(AVERAGE(INDEX(D$2:D2,N(IF(1,AGGREGATE(14,6,ROW(D$2:D2)-ROW(D$1)/(A$2:A2=A2),{1;2;3;4;5;6;7}))))),"")

Regards
 

dsb1985

New Member
Please try at
F2
=AVERAGEIFS(D$2:D2,A$2:A2,A2,E$2:E2,">"&WORKDAY(E2,-3))

G2
=AVERAGEIFS(D$2:D2,A$2:A2,A2,E$2:E2,">"&WORKDAY(E2,-7))
this formula calculate 3dma from 1st line item, but the 3dma should be start from 3rd line item, means first two rows should be blank
 
Last edited:

dsb1985

New Member
Maybe,

1] "3DMA" in F2, formula copied down :

=IFERROR(AVERAGE(INDEX(D$2:D2,N(IF(1,AGGREGATE(14,6,ROW(D$2:D2)-ROW(D$1)/(A$2:A2=A2),{1;2;3}))))),"")

2] "7DMA" in G2, formula copied down :

=IFERROR(AVERAGE(INDEX(D$2:D2,N(IF(1,AGGREGATE(14,6,ROW(D$2:D2)-ROW(D$1)/(A$2:A2=A2),{1;2;3;4;5;6;7}))))),"")

Regards
perfectly works, but it hangs the excel, don't know the reason or may be due to complex formula equation.
 
this formula calculate 3dma from 1st line item, but the 3dma should be start from 3rd line item, means first two rows should be blank
F2
=IF(COUNTIFS(A$2:A2,A2,E$2:E2,">"&WORKDAY(E2,-3))<3,"",AVERAGEIFS(D$2:D2,A$2:A2,A2,E$2:E2,">"&WORKDAY(E2,-3)))

G2
=IF(COUNTIFS(A$2:A2,A2,E$2:E2,">"&WORKDAY(E2,-7))<7,"",AVERAGEIFS(D$2:D2,A$2:A2,A2,E$2:E2,">"&WORKDAY(E2,-7)))
 

bosco_yip

Excel Ninja
the stock data have more than 75000 lineitems, may be this is the reason for slow response of spreadsheet.
This problem can be solved by adopt another formula, but due to your limited information, unable to give further help in this moment.
 

dsb1985

New Member
This problem can be solved by adopt another formula, but due to your limited information, unable to give further help in this moment.
Dear,
thanks for your prompt responses.
due to heavy file size unable to upload the data file, can you copy the same data upto 40000 to 50000 lineitem and copy the same formula down, you can see the process time & hangness issue in your sheet.
 
Top