D. P. Saville

Recently I needed a Standard Deviation function for a set of values where it wasn't appropriate to use the built-in SQL StDev function.

If you need statistics in your Access database, why not purchase the FMS Statistics product from us.

I therefore created the following as_StDev function in Access 97 using a ParamArray so that the number of arguments it can take is variable (and is better than using lots of Optional arguments since that would still have a maximum number or arguments and also lead to much messier code). The "For Each varX In pvarArray" loop is the heart of the function that cycles through the ParamArray, creating the intermediate working values varAccum, varAccumSquares, and intN, which are then plugged into the normal Standard Deviation calculation (it's described in Excel's on-line help if you want to refresh your memory).

The function can easily be adapted for other similar functions such as the Minimum values from a variable group of numbers, the Maximum value (these two will work with strings as well as numeric values), the Average value, and the Variance, as well as for more advanced or obscure functions including those not available in SQL.

Public Function as_StDev(ParamArray pvarArray())

' Standard Deviation for a set of numeric arguments.

' Uses the non-biased n-1 version for a samples

'Conversion for whole population in comments

' Environment : Access 97

' Use : dblStdDev = as_StDev(22,27,18,28,31,16)

' which gives 5.9553897584703

'

Dim varX As Variant, varAccum As Variant

Dim varAccumSquares As Variant

Dim intN As Integer, varWork As Variant

For Each varX In pvarArray

If Not IsNull(varX) Then

varAccum = varAccum + VarX

varAccumSquares = varAccumSquares + (varX * varX)

intN = intN + 1

End If

Next

If intN < 3 Then ' too small a population

as_StDev = 0

Exit Function

End If

varWork = ((intN * varAccumSquares) - _

(varAccum * varAccum)) / _

(intN * (intN - 1))

' replace with (intN * int)N for entire populations.

If varWork < 0.001 Then

as_StDev = 0 ' All values the same

Else

as_StDev = Sqr(varWork)

End If

End Function