Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Standard Deviation on last 3 rows


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

Iteration NameTotal Points Accepted This SprintVelocity (avg of last 3 Iter)Velocity Variance (   STDEV.S(of last 3 iterations)/AVERAGE(of last 3 iterations)   )
PI4 IT522  
PI4 IT610  
PI1 IT120  
PI1 IT227  
PI1 IT328  
PI1 IT427  
PI1 IT527  
PI1 IT613  
PI2 IT122  
PI2 IT232  


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?


ACE Emeritus
ACE Emeritus

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!



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

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





ACE Emeritus
ACE Emeritus

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).



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?



ACE Emeritus
ACE Emeritus

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

  (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)

Hope that helps!



That is perfect, Than kyou Sir.