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?
To confirm, here is the Debug.Print of my subsets: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
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