Excel Formula Conversion - Multi-row Standard Deviation

Question

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?

STDEV.P Example Data.xlsx

kkoenig · Accepted Answer

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.