Dynamic Standard Deviation VBA

[slamdunka]

Hi

I am working on a project where I am trying to find the standard deviation within a dynamic range.

I am having some trouble as to how to calculate the standard deviation.

I am trying to do this step by step, but I cannot get the sum difference of squares working

Here a link to a screen shot of the spreadsheet

http://i.imgur.com/uRzl0nH.jpg

Sub RunStDev()
Dim WS As Worksheet
Dim maxrow As Double, i As Double, k As Double


Dim name As String
Dim stdev As Double
Dim nrows As Double
Dim tot As Double
Dim Mean As Double
Dim SumSq As Double

'---> Set Variables
Set WS = ActiveSheet
maxrow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row
tot = 0
k = 0


'---> Clear Col C
WS.Range("N:N").ClearContents


'---> Sort Worksheet by Date
WS.Range("A7:N" & maxrow).Sort key1:=WS.Range("B1"), order1:=xlAscending, Header:=xlYes
name = WS.Cells(8, "B")


'---> Start Process
For i = 8 To maxrow + 1
    If WS.Range("B" & i) <> name Then
        WS.Cells(i - 1, "N") = tot
        tot = 0
        WS.Cells(i - 1, "o") = k
        k = 0
        WS.Cells(i - 1, "p") = Mean
        Mean = 0
        WS.Cells(i - 1, "q") = Sumsq
        sumsq = 0
        name = WS.Cells(i, "B")
    End If
    
    tot = WS.Cells(i, "m").Value + tot
    
    k = k + 1
    
    Mean = tot / k
    
    Sumsq = Sumsq + (WS.Cells(i,"m").value - Mean)^2
Next i


MsgBox ("stdev inserted in Col N by name successfully.")


End Sub

 

[mikerickson]

Cross Posted Dynamic Standard Deviation VBA

 

[slamdunka]

sorry, just trying to find a way around this, spending a lot of time playing around and nothing is working