Standard Deviation - Multi Row Formula Tool

[nsessa]

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!

[Luke_C · Accepted 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. 

[apathetichell]

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.