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
If intN < 3 Then ' too small a population
as_StDev = 0
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
as_StDev = Sqr(varWork)