Hello,

I am trying to create a function in VBA and I seem to be a bit stuck. I have created 5 subsets an they all work fine. What I then want to do is calculate the standard deviation of my subsets and take the average standard deviation. But my formula for standard deviation (in red) seems to not produce any results. I have tried searching and cannot find a solution anywhere. I have checked that my subsets contains only numerical values. Code detailed below:. Have i defined something incorrectly?

Function FITVol(rng As Range) As Double
    Dim subsets(1 To 5) As Variant
    Dim subsetStDev(1 To 5) As Double
    Dim sumStDev As Double
    Dim lastRow As Long, i As Long, j As Long
    ' Find the last row with data in the range
    lastRow = rng.Rows.Count
    Debug.Print "Last Row: " & lastRow
    ' Loop through the data, calculate subsets, and store the natural log of the ratio in subsets array
    For i = 1 To 5
        For j = i + 5 To lastRow Step 5
            subsets(i) = subsets(i) & WorksheetFunction.Ln(rng.Cells(j, 1).Value / rng.Cells(j - 5, 1).Value) & ","
        Next j
        ' Remove the trailing comma and split the string into an array
        subsets(i) = Split(Left(subsets(i), Len(subsets(i)) - 1), ",")
    Debug.Print "Subset " & i & ": " & Join(subsets(i), ",")
    Next i
    'Calculate standard deviation for each subset and store in subsetStDev array
    For i = 1 To 5
        subsetStDev(i) = WorksheetFunction.StDev(subsets(i))
        ' Output standard deviation to Immediate Window for debugging
        Debug.Print "Subset " & i & " Standard Deviation: " & subsetStDev(i)
    Next i
    ' Calculate the average of standard deviations
    For i = 1 To 5
        sumStDev = sumStDev + subsetStDev(i)
    Next i
    ' Calculate the final result (average of standard deviations)
    FITVol = sumStDev / 5
    Debug.Print "Average of Standard Deviations: " & FITVol
End Function
To confirm, here is the Debug.Print of my subsets:

Subset 1: -1.40268043784976E-02, 6.42673935161397E-02, -0.030939031938565, -2.06996998946987E-02, -5.59296897453847E-02, -3.29835990824823E-02, 5.05237075458735E-02, -2.34609143908246E-02
Subset 2: 1.96408478699902E-02, 4.08259753474539E-02, -3.52824173944672E-02, -3.37172783773844E-02, -4.67909355838842E-02, 8.93980019460165E-03, -5.94079251496369E-03, 0.014801380302262
Subset 3: 7.00126242265415E-03, 5.57446037672143E-03, 1.65253397538366E-02, -0.024891558388088, -6.35994296943924E-02, 5.94079251496354E-03, -5.94079251496369E-03, 2.35990690296357E-02
Subset 4: 1.24711654861162E-02, 1.91048585927042E-02, -3.57678825722095E-02, -1.98075017901463E-02, -6.33858776030418E-02, 2.10748320866318E-02, 2.21181866417995E-02, 0
Subset 5: 6.55952170294352E-02, -4.87921952056971E-02, -3.38932174496871E-02, -5.31116971661447E-02, -1.52689037915363E-02, 3.62489625936878E-02, 2.34609143908245E-02