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.
GreinerjrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.