Standard deviation of a subset in VBA

[SHW2022]

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

[Dave]

Hi SHW2022 and welcome to this forum. I'm guessing that you're not familiar with XL forums and haven't taken the time to read the posting guidelines. Cross posting is OK if you provide a link to other sites where you have posted the same help request. All forums have similar guidelines and FYI, many members frequent many sites. I'll provide this one link for you and then maybe have a look at your code. Dave
Standard deviation of a subset in VBA | MrExcel Message Board

[Dave]

Not sure what's up with your code. Here's some code borrowed from Tushar Meta for 1D array..

Function StdDev(Arr)
     Dim i As Integer
     Dim avg As Single, SumSq As Single
     Dim k1 As Long, k2 As Long
    
     Dim n As Long
    
     k1 = LBound(Arr)
     k2 = UBound(Arr)


     n = 0
     avg = Mean(Arr)
     For i = k1 To k2
        n = n + 1
          SumSq = SumSq + (Arr(i) - avg) ^ 2
     Next i
 
     StdDev = Sqr(SumSq / (n - 1))


End Function

You could trial it like this...

For i = 1 To 5
    subsetStDev(i) = StdDev(subsets(i))
    ' Output standard deviation to Immediate Window for debugging
    Debug.Print "Subset " & i & " Standard Deviation: " & subsetStDev(i)
Next i

Good luck. Dave

[Aussiebear]

Microsoft suggests the STDEV has been replaced by STDEV.S. New and "Improved" by their reckoning.

[p45cal]

First, nothing wrong with cross posting as long as you share where you've cross posted to. Have a read of https://excelguru.ca/a-message-to-forum-cross-posters/ to understand why, and more importantly, to ensure you continue to get responses.

You said: "I have checked that my subsets contains only numerical values"
Actually, they're all strings.

Try:

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, idx
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
  ReDim a(1 To 1)
  idx = 0
  For j = i + 5 To lastRow Step 5
    idx = idx + 1
    ReDim Preserve a(1 To idx)
    a(idx) = WorksheetFunction.Ln(rng.Cells(j, 1).Value / rng.Cells(j - 5, 1).Value)
  Next j
  subsets(i) = a
  'Calculate standard deviation for each subset and store in subsetStDev array
  subsetStDev(i) = Application.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
FITVol = Application.Average(subsetStDev)
Debug.Print "Average of Standard Deviations: " & Application.Average(subsetStDev)
End Function

Bear in mind what Aussiebear said, and perhaps choose something different for Application.StDev(subsets(i)). You have choices:

2024-04-18_231701.jpg

You can see their equivalents and explanations if you start entering the stdev function in a cell:

2024-04-18_231851.jpg

Later, when you've debugged it fully, you'll be able to do away with the intervening subsets(1 To 5) array:

Function FITVol(rng As Range) As Double
Dim subsetStDev(1 To 5) As Double
Dim lastRow As Long, i As Long, j As Long, idx

lastRow = rng.Rows.Count
For i = 1 To 5
  ReDim a(1 To 1)
  idx = 0
  For j = i + 5 To lastRow Step 5
    idx = idx + 1
    ReDim Preserve a(1 To idx)
    a(idx) = WorksheetFunction.Ln(rng.Cells(j, 1).Value / rng.Cells(j - 5, 1).Value)
  Next j
  subsetStDev(i) = Application.StDev(a)
Next i
FITVol = Application.Average(subsetStDev)
End Function

[snb]

Subset 1 : A1:H1
Subset 2 :A2:H2 , etc

Sub M_snb()
  sn = Cells(1).CurrentRegion.Resize(, Cells(1).CurrentRegion.Columns.Count + 1)
  
  For j = 1 To UBound(sn)
    For jj = 1 To UBound(sn, 2) - 1
      sn(j, UBound(sn, 2)) = sn(j, UBound(sn, 2)) + sn(j, jj)
    Next
    sn(j, UBound(sn, 2)) = sn(j, UBound(sn, 2)) / (jj - 1)
    
    x = 0
    n = 0
    For jj = 1 To UBound(sn, 2) - 1
      If sn(j, jj) <> "" Then x = x + (sn(j, jj) - sn(j, UBound(sn, 2))) ^ 2
      n = n + Abs(sn(j, jj) = "")
    Next
    
    y = y + Sqr(x / (jj - n - 2))
    c00 = c00 & vbLf & Sqr(x / (jj - n - 2))
 Next
 
 MsgBox c00 & vbLf & vbLf & y / UBound(sn)
End Sub

[p45cal]

Not acknowledging the help you get guarantees you'll get help again. Oh wait…

[Aussiebear]

I'd say SHW2022 wont be back, given that he's receiving help at Mr Excel, up until yesterday evening. Mind you, he didn't apologise for cross posting over there either, nor has he offered any thanks for the assistance that has been offered. At your discretion, please feel free to put the User on a Ignore listing.

[SHW2022]

Hi SHW2022 and welcome to this forum. I'm guessing that you're not familiar with XL forums and haven't taken the time to read the posting guidelines. Cross posting is OK if you provide a link to other sites where you have posted the same help request. All forums have similar guidelines and FYI, many members frequent many sites. I'll provide this one link for you and then maybe have a look at your code. Dave
Standard deviation of a subset in VBA | MrExcel Message Board

Apologies, for not posting the link I shall be sure to do this in the future.

[SHW2022]

Apologies, I should have responded earlier, I was building this for a project at work and have been sidetracked until now. Thank you to everyone for taking the time to help me.

[SHW2022]

I am sorry for any offence caused. You are correct that the optimal solution has been provided to me on the Mr Excel site and the link for that is above, I repeat here just for clarity (https://www.mrexcel.com/board/thread...n-vba.1257499/).

However, that solution wasn't perfect and I was trying to ensure it was working correctly before I came back to everyone. You are correct though that I should have provided some thanks and acknowledgement before today given that everyone had given up their time to help me.

I'm still working on error proofing my formula and once I've done that I'll post the finalized code on both forums.