I’m sure there’s a rookie error in this code but I can’t see it. Im loading daily values, broken up by country into an array and trying to use the array values to get a standard deviation. The raw data is more countries than I care about so there’s population data only for countries a need. The values in the array are correct but the standard deviation is yielding nothing but zero values. Any ideas?
Sub StandardDeviation() Dim i As Variant Dim j As Variant Dim std As Double Dim lastrow As Long Dim rng As Range Dim country As String Dim region As String Dim population As Long Dim dayi As Date Dim dayj As Date Dim count As Long Dim dncj As Double Dim dnc() As Double lastrow = Cells(Rows.count, "a").End(xlUp).Row Set rng = Range("m5:m" & lastrow) rng.ClearContents Set rng = Range("l5:l" & lastrow) For Each i In rng population = Cells(i.Row, "h").Value count = 0 If population <> 0 Then country = Cells(i.Row, "b").Value dayi = Cells(i.Row, "c").Value For Each j In rng region = Cells(j.Row, "b").Value dncj = Cells(j.Row, "l").Value If country = region And dayi > dayj Then ReDim Preserve dnc(count) dnc(count) = dncj count = count 1 End If Next j For j = 0 To UBound(dnc) std = Application.WorksheetFunction.StDev_P(dnc(j)) Cells(i.Row, "m").Value = std Next j ElseIf population = 0 Then Cells(i.Row, "m").Value = 0 End If Next i End Sub