Average and Standard Deviation over set number of cells in a column

[JanLo]

Hi all – I need your expertise.  I need to mirror the Excel workings for a large amount of data (10 years worth) and I’m stuck.  I have Date and “Just Yield “ for my input.  The average and standard deviation are calculated over 20 Cells of data for each value.  I’m having trouble replicating this on Alteryx.  I’m guessing a multi tool could do it but I can’t seem to work it out.

I worked out how to do the daily change, after which I needed a lie down!

Any ideas how I could replicate average and standard deviation for the same output as Excel attached?

Excel Formula below

Daily Change

= (B2-B3)*100

Standard Deviation

= STDEV.S(C2:C21)… = STDEV.S(C3:C22)…etc

Average

= AVERAGE(B2:B21)… = AVERAGE(B3:B22) etc

[Kakuffo]

hi @JanLo the Summarize tool should achieve this for you First filter down the data you would like to analyze group it if it needs grouping and then apply the Avergae and Standard Deviations functions

[DavidSkaife · Accepted solution]

Hi @JanLo 

I've built this iterative macro that seems to do what you require:

It's set to a maximum iteration of 100, but you can change this in the Interface Designer

Let me know how you get on with it!

[JanLo]

perfect!  thank you!