Need dynamic formula to create average and standard deviation data

[MichelleMitchellLutz]

Hi! I have a data set with lots (30+) metrics and multiple data names in the Field1 column. I know I could use a Summarize and basic Formula tool to calculate the Average, Standard Deviation, and Coefficient of Variation. However, there are many metrics and new ones could be requested. I tried to use the CReW Dynamic Formula macro but I don't think it's working because my field names are not unique. Any thoughts on a more dynamic approach?

Raw Data

Desired Outcome

Not successful CReW Macro output

[KilianL · Accepted solution]

Hi @MichelleMitchellLutz ,

the most useful tool for dynamic workflows is the Transpose, often combined with the Summarize and Cross-Tab.

By transposing all columns except Field1, the option 'dynamic and unknown columns' makes sure to transpose any new Metrics in your dataset. The Summarize is able to get Avg and StdDev for each metric.

Find an example attached.

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx

[MichelleMitchellLutz]

Thank you so much! I couldn't wrap my mind around it, but this is perfect. Have a great weekend!