Consulting

Results 1 to 11 of 11

Thread: Standard deviation of a subset in VBA

  1. #1
    VBAX Newbie
    Joined
    Apr 2024
    Posts
    4
    Location

    Standard deviation of a subset in VBA

    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

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    855
    Location
    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

  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    855
    Location
    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

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Microsoft suggests the STDEV has been replaced by STDEV.S. New and "Improved" by their reckoning.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    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
    Last edited by p45cal; 04-18-2024 at 04:06 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    snb
    Guest
    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

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Not acknowledging the help you get guarantees you'll get help again. Oh wait…
    Last edited by p45cal; 04-24-2024 at 04:17 AM.

  8. #8
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    VBAX Newbie
    Joined
    Apr 2024
    Posts
    4
    Location
    Quote Originally Posted by Dave View Post
    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.

  10. #10
    VBAX Newbie
    Joined
    Apr 2024
    Posts
    4
    Location
    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.

  11. #11
    VBAX Newbie
    Joined
    Apr 2024
    Posts
    4
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •