(Solved) – StDev_P yielding nothing by 0 values


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)


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

Leave a Reply

Your email address will not be published. Required fields are marked *