Excel Formula Conversion - Multi-row Standard Deviation

[kkoenig]

I'm trying to transition a process from Excel into Alteryx and am getting hung up on a standard deviation calculation. 

I've attached some sample data. It has rate values for different markets by quarter, and the current formula in excel is: 

=STDEV.P(IF($A$1:[@Market]=[@Market],$C$1:[@Rate]))

Where column A is the market name, and column C is the rate value. So for each row, the standard deviation is calculated from that row's rate and every row above it with the same market name. 

This macro replicates the STDEV.P excel formula, but I am at a loss as to how to group by market and iterate each set of rows through it.

I've thought of doing a multi-row formula similar to what is discussed here, but the number of quarters/rows is not always consistent for each market. 

Any thoughts? 

[apathetichell]

I'm seeing some rounding here - and my hunch is that it ties into how Alteryx is dealing with the exponents. At it's core:

1) create a tile tool (unique record  -market)

2) running total of rate (group by market)

3) using a formula tool - 

1) create a mean as running total/tile_sequence number

2) create a mean square as pow(rate-mean),2

3) subtract 1 from tile sequence

4) running total on market. sum your mean square field.

formula tool:

check for if tile seuqence is zero - if so zero.

else pow(running total of mean square/tile sequence,.5)

[kkoenig · Accepted solution]

Wow, that is 10x more elegant that what I was working on! You got me on the right track and I think I've got a (messy) solution. Thank you so much! 

Attaching the workflow here for anyone who might need something similar in the future. I have no clue if so many grouping/tile/sort fields were actually necessary, but it worked and that's all that mattered at the moment ¯\_(ツ)_/¯

Thank you again for the help, @apathetichell! You're a lifesaver.