Hi Alteryx Community,
I am a bit stumped on a problem I am having trying to calculate standard deviation of the past 6 iterations of data using the multi row formula tool. I would prefer a solution that allows me to use this tool.
My data is broken out as follows as an example: I am trying to be able to capture the standard deviation of the past 6 iterations (Row -6 Actuals vs projection: Row-1 Actuals vs Projection). Using Excel I calculate stdev as 9.85%. However using my current formula in Alteryx I get 11.74% as stdev
Actuals vs R6M Projection
- 19.54%
-18.43%
- 2.80%
-4.46%
-1.74%
8.63%
Here is my current Alteryx multi formula tool formula to calculate standard deviation. What am I doing wrong?
SQRT(
(POW([Row-6:Actuals vs R6M projection],2)+
POW([Row-5:Actuals vs R6M projection],2)+
POW([Row-4:Actuals vs R6M projection],2)+
POW([Row-3:Actuals vs R6M projection],2)+
POW([Row-2:Actuals vs R6M projection],2)+
POW([Row-1:Actuals vs R6M projection],2)
)/6
)
Thank you!
Solved! Go to Solution.
It looks like you might be using the 'STDEV.P' or 'STDPA' function in excel to come up with the 9.85% number. The way excel calculates the standard deviation with those formulas is different than the formula you've written in Alteryx.
Excel's logic is:
To reproduce it you need to factor in subtracting the average in the numerator.
And to prove the @Luke_C is correct - if you run the same calculations in excel - you'll get the same figure you got in Alteryx. One note - is this a population or a sample? If it's a sample you'll have a slightly different STDEV formula.
If you run STDEV through Summarize as attached you get the same result as STDEV (Sample) in Excel... Hope this helps.