Need dynamic formula to create average and standard deviation data

Question

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

Mitchell-Lutz Dynamic Formula for Avg Stnd Dev.yxmd

KilianL · Accepted Answer

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