• 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

D

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

  • dma.xlsx
    12.5 KB · Views: 8
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.
 
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​
 
Sir,

i think there is some error in formula, please see the below screen..
also find the attach sheet...

73328
 

Attachments

  • dma v2.xlsx
    19.6 KB · Views: 5
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
 
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))
 

Attachments

  • dma v2.xlsx
    18.7 KB · Views: 5
Excel Wizard said:
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))
Click to expand...
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:
bosco_yip said:
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
Click to expand...
perfectly works, but it hangs the excel, don't know the reason or may be due to complex formula equation.
 
dsb1985 said:
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
Click to expand...

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)))
 
dsb1985 said:
the stock data have more than 75000 lineitems, may be this is the reason for slow response of spreadsheet.
Click to expand...

This problem can be solved by adopt another formula, but due to your limited information, unable to give further help in this moment.
 
bosco_yip said:
This problem can be solved by adopt another formula, but due to your limited information, unable to give further help in this moment.
Click to expand...
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.
 
You must log in or register to reply here.
Back
Top