[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1448
  • Last Modified:

Using VB 6.0 how do I find the Standard Deviation in list of numbers.

Using VB6.0 I need to find the Standard Deviation in a list of numbers.  I have tried STDEVPA (was not recognized by VB).   I tried DSTDev with the beginning and end of list (row,col) as arguments

For Col = 1 To TotCols
       ST = DStDev(Vars(1, Col), Vars(NumRead, Col))
Next Col
got error 2950 - Reserved Error

If I do not have the correct reference turned on to use the above, let me now which one.
0
Greinerjr
Asked:
Greinerjr
  • 2
  • 2
1 Solution
 
PaulHewsCommented:
Here's a function you can use.
Option Explicit
 
Private Sub Command1_Click()
    Dim dbl(3) As Double
    dbl(0) = 5
    dbl(1) = 6
    
    dbl(2) = 8
    dbl(3) = 9
    
    Debug.Print StdDev(dbl, True)
    
End Sub
 
 
 
Private Function StdDev(dbl() As Double, IsSample As Boolean) As Double
    'Uses sample calculation if IsSample = True and population calculation if IsSample = False
    Dim dblMean As Double
    Dim dblTotal As Double
    Dim dblDev As Double
    Dim i As Long
    
    If UBound(dbl) = LBound(dbl) Then
        Err.Raise 11, "StdDev"  'Division by zero...
    End If
    
    
    For i = LBound(dbl) To UBound(dbl)
        dblTotal = dblTotal + dbl(i)
    Next
    dblMean = dblTotal / (UBound(dbl) - LBound(dbl) + 1)
    
    For i = LBound(dbl) To UBound(dbl)
        dblDev = dblDev + (dblMean - dbl(i)) ^ 2
    Next
    If IsSample Then
        dblDev = dblDev / (UBound(dbl) - LBound(dbl))
    Else
        dblDev = dblDev / (UBound(dbl) - LBound(dbl) + 1)
    End If
    
    StdDev = Sqr(dblDev)
    
End Function

Open in new window

0
 
GreinerjrAuthor Commented:
Your routine worked, however the answer did not exactly match the result I get in EXCELL as shown in the attache file and below.  Also, I don't understand the difference in True or False but I did find there was a slight difference in the answer.  Which way should I use It?  Let me know why I can't match the EXCELL answer.  The code I am using is in the Code Snippet.

The debug output is in the attached .xls file.


Debug.Print "": Debug.Print "From: cmdProcess_Click     " & Now
ReDim dbl(NumRead)                                 'Change list size to match no records processed.
For Col = 1 To TotCols
      For Row = 1 To NumRead: dbl(Row) = 0: Next Row     'Initialize
      
      For Row = 1 To NumRead               'Create the dbl list
            dbl(Row) = Vars(Row, Col)      'Put number in dbl list
            If Col = 15 Then Debug.Print "Row = " & Row & "    Col = " & Col, dbl(Row)    'debugger to show only col 15 of the Vars array
      Next Row
      MyTot(Col, 4) = StdDev(dbl, False)  'Get Std. dev. on this list
      If Col = 15 Then Debug.Print "Standard Deviation: " & MyTot(Col, 4)                         'This Looks maybe right?????? Experts Exchange
Next Col

Open in new window

TEst-of-standard-dev.xls
0
 
GreinerjrAuthor Commented:
I am going to accept your solution as the answers approximate EXCELL STDEVP fairly closely.  Thanks for your help.

John
0
 
PaulHewsCommented:
My code reproduces Excel's StdevP and Stdev exactly as I show here.   (stdevp is population, so pass false.  stdev is sample, so pass true.)

It is most likely that you are either reading too few items in the list, or too many.  Zeroes in the array will throw off the calculation.

I think the B grade might have been a little hasty, what do you think?
Option Explicit
 
Private Sub Command1_Click()
    Dim dbl(10) As Double
    
 
dbl(0) = 30
dbl(1) = 20
dbl(2) = 10
dbl(3) = 50
dbl(4) = 50
dbl(5) = 60
dbl(6) = 20
dbl(7) = 10
dbl(8) = 50
dbl(9) = 60
dbl(10) = 40
 
 
    
    Debug.Print StdDev(dbl, False) ' 18.227216050694  Excel StdevP: 18.22721605
 
    Debug.Print StdDev(dbl, True)  ' 19.1168654714769  Excel Stdev: 19.11686547
 
    
End Sub

Open in new window

0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now