Standard Deviation on last 3 rows

[dlesny]

Hello. I am trying to calculate StnDev for the Team's Velocity, which is StnDv from Avg of last 3 Itertions.

Iteration Name	Total Points Accepted This Sprint	Velocity (avg of last 3 Iter)	Velocity Variance (   STDEV.S(of last 3 iterations)/AVERAGE(of last 3 iterations)   )
PI4 IT5	22
PI4 IT6	10
PI1 IT1	20
PI1 IT2	27
PI1 IT3	28
PI1 IT4	27
PI1 IT5	27
PI1 IT6	13
PI2 IT1	22
PI2 IT2	32

I found the Velocity which I can do with Multi Row Formula:

AVERAGE([Row-1:Total Points Accepted This Sprint],[Row-2:Total Points Accepted This Sprint],[Row-3:Total Points Accepted This Sprint])

But how to do the Velocity Variancefor the last 3 iterations?

[JohnJPS]

Hi @dlesny,

The Variance of that Velocity would be another multi-row formula, and the StdDev would be the square root of the variance; I've attached an example using your data, which hopefully looks kind of like what you're after.

Hope that helps!

John

[dlesny]

Thank you for your help. I am not getting quite the same results like Excel. STDEV.S uses the following formula:

so =STDEV.S(B10:D10) (32,22,13) returns 9.504384953 and with this workflow 7.79601

[JohnJPS]

I think it's due to me focusing on the row-1/row-2/row-3 for "previous 3"... if I change it to currentRow/row-1/row-2, then I can get 7.79601 to appear in the output. (Please see attached).  So, it really depends on what you want to compare to: deviation from previous (my first approach), or deviation when including your in result in calculation of the previous (the new approach).

[dlesny]

Hi John

Thank you for your help again.

I have spoted that and altered before, that's not the issue however.

For (32,22,13) Excel returns 9.504384953 with STDEV.S

I need Alteryx to return the same value with the same data and atm it returns 7.79601. Any idea?

[JohnJPS · Accepted solution]

I think I was overthinking things with multiple Multi-Row formulas.  To reproduce that, we can just plug it into a single such formula:

SQRT(
  (POW([Points]       - AVERAGE([Points],[Row-1:Points],[Row-2:Points]),2) +
   POW([Row-1:Points] - AVERAGE([Points],[Row-1:Points],[Row-2:Points]),2) +
   POW([Row-2:Points] - AVERAGE([Points],[Row-1:Points],[Row-2:Points]),2)
  )/2
)

Hope that helps!

John 

[dlesny]

That is perfect, Than kyou Sir.