Standard Deviation Formula

[George Too]

Hi all,

Does any one out there have the Standard Deviation Formula written in VBA?

I have one but it's off by 1/1000

Thanks,

George

 

[Mile-O]

Can't you use the DStDev aggregate function?

If you think about it logically you should be able to write the code as small chunks.

So where:

Square root of ((( n multiplied with the sum of x squared) - (the sum of x)squared) divided by (n multiplied by (n minus 1)))

I'll give it a shot...

 

[Mile-O]

Okay, had a shot at creating a function that calculates the standard deviation.

Function StandardDeviation(ByVal strTable As String, ByVal strField As String)


    ' set up a recordset to navigate through
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTable)
    
    ' dimension some variables
    Dim intCounter As Integer, dblSquareTotal As Double, dblTotal As Double
    ' move to the first record in our recordset
    rs.MoveFirst
    
    ' do the sum part of the function by going through the records rather than use the aggregate
    ' functions as with numerous records it would be too slow
    For intCounter = 1 To rs.RecordCount
        dblSquareTotal = dblSquareTotal + (rs.Fields(strField) * rs.Fields(strField))
        dblTotal = dblTotal + rs.Fields(strField)
        rs.MoveNext
    Next intCounter

    Dim lngNumberOfSamples As Long
    
    ' get number of samples
    lngNumberOfSamples = rs.RecordCount
    
    ' break the formula into smaller bits to see how it operates
    Dim dblComponents(1 To 3) As Double
    
    ' assign each part of the formula
    dblComponents(1) = lngNumberOfSamples * dblSquareTotal
    dblComponents(2) = dblTotal * dblTotal
    dblComponents(3) = llngNumberOfSamples * (lngNumberOfSamples - 1)
    
    ' calculate the standard deviation
    StandardDeviation = Sqr((dblComponents(1) - dblComponents(2)) / dblComponents(3))

    ' display the standard deviation
    MsgBox StandardDeviation

End Function

You could definitely simplify it as I split it into bits to show you it working - but it was very exact with the sample data I gave it.

 

[Mile-O]

I tidied it up again, reducing the use of variables, and adding error handling.

Function StandardDeviation(ByVal strTable As String, ByVal strField As String) As Double

   On Error Goto Err_StandardDeviation

    ' Author: Mile-O-Phile
    ' History: 25-Feb-2003 - Initially written
    ' Inputs: tablename (strTable) - the recordset upon which the
    '              function will take its range of samples from
    '              fieldname (strField) - the field in the recordset (strTable)
    '              from which the Standard Deviation will be calculated
    ' Process: calculates two main parts of the Standard Deviation
    '               formula which would have been difficult to do without
    '              a pre-defined formula
    '               using the values determined from the first process
    '               the two variables (dblSquareTotal and dblTotal) are
    '               used in the Standard Deviation formula to produce 
    '               the final output
    ' Output: the Standard Deviation
    ' Notes: This function will only work where the source data
    '             being referred to is held within a recordset ad can not
    '             calculate the Standard Deviation if the sample data is
    '             held within an array.

    ' set up a recordset to navigate through
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTable)
    ' move to the first record in our recordset
    rs.MoveFirst
    
    ' dimension some variables
    Dim intCounter As Integer, dblSquareTotal As Double, dblTotal As Double
    
    ' do the sum part of the function by going through the records
    ' rather than use the aggregate
    ' functions as with numerous records it would be too slow
    For intCounter = 1 To rs.RecordCount
        dblSquareTotal = dblSquareTotal + (rs.Fields(strField) * rs.Fields(strField))
        dblTotal = dblTotal + rs.Fields(strField)
        rs.MoveNext
    Next intCounter
       
    ' calculate the standard deviation
    StandardDeviation = Sqr(((rs.RecordCount * dblSquareTotal) - (dblTotal * dblTotal)) / (rs.RecordCount * (rs.RecordCount - 1)))

    ' finally, close the recordset
    rs.Close

Exit_StandardDeviation:
    Exit Function

Err_StandardDeviation:
    MsgBox Err.Description, vbCritical, "Error number " & Err.Number & " has occurred."
    Resume Exit_StandardDeviation

End Function

 

[George Too]

Thanks for your effort Mile-O-Phile, I'll surely give it a shot tonite.

Regards,
George