StDev

Signature#

Function StDev(ByVal vArr As Variant) As Double

Parameters#

Returns#

Double. Sqr(M2 / n), where n is the number of accepted numeric values and M2 is their accumulated squared deviation. Returns 0 for no observations, a single observation, or rejected input shapes.

Example#

Option Explicit

Public Sub ExampleFinanceStDev()
    Dim f As New Finance
    Dim values As Variant
    values = VBA.Array(2, 4, 4, 4, 5, 5, 7, 9)
    Debug.Print f.StDev(values) '2 (population standard deviation)
End Sub

Behavior and edge cases#

• Accepts an allocated 1D array or a 2D array. A 1D array is expanded through the global Arr instance declared in a standard module. For 2D input, only the column at LBound(vArr, 2) is read; additional columns are ignored.
• Loops over the actual first-dimension lower and upper bounds. A horizontal 2D vector therefore contributes only its first cell; reduce it to 1D first if every cell should be included.
• The input is passed ByVal and is not deliberately modified. Non-array input or a detected rank other than 1 or 2 returns the default numeric value 0.
• Error values, Null and Empty are skipped before IsNumeric is evaluated. Numeric text is accepted and converted with CDbl using VBA locale rules; text that is not numeric is skipped. VBA Boolean and Date values are not numeric for IsNumeric.
• There is no overflow recovery. Values that pass IsNumeric but cannot be converted or accumulated within Double limits can raise a runtime error.
• Updates the count, running mean and M2 in one pass using Welford-style updates. The divisor is n, not n-1; this is population standard deviation.
• If floating-point rounding leaves M2 negative, the implementation clamps it to zero before taking the square root.
• A result of 0 can mean no numeric observations, one observation, or identical observations. Numeric results remain subject to Double precision and overflow limits.

Performance#

O(n) time for n rows in the first column, with O(1) accumulator state. The 1D-to-2D normalization allocates O(n) elements; passing a Variant array ByVal can also require an array copy.

Function StDev(ByVal vArr As Variant) As Double
    'Returnds stdof a variant

    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim n                       As Long
    Dim vValue                  As Variant
    Dim dblMean                 As Double
    Dim dblValue                As Double
    Dim dblDelta                As Double
    Dim dblM2                   As Double

    If Not IsArray(vArr) Then Exit Function
    If Arr.DimCounter(vArr) = 1 Then vArr = Arr.DimExpand(vArr)
    If Arr.DimCounter(vArr) <> 2 Then Exit Function

    lCol = LBound(vArr, 2)

    For lRow = LBound(vArr, 1) To UBound(vArr, 1)
        vValue = vArr(lRow, lCol)
        If Not IsError(vValue) Then
            If Not IsNull(vValue) Then
                If Not IsEmpty(vValue) Then
                    If IsNumeric(vValue) Then
                        'welford algoritlhm
                        dblValue = CDbl(vValue)
                        n = n + 1
                        dblDelta = dblValue - dblMean
                        dblMean = dblMean + dblDelta / n
                        dblM2 = dblM2 + dblDelta * (dblValue - dblMean)
                    End If
                End If
            End If
        End If
    Next lRow

    If n = 0 Then Exit Function
    If dblM2 < 0 Then dblM2 = 0

    StDev = Sqr(dblM2 / n)
End Function