Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.
Function RowStats(Stat As String, ParamArray Vars())
Dim Numerator As Double
Dim Denominator As Double
Dim Counter As Long
Dim Result
Select Case UCase(Stat)
Case "MIN"
Result = Vars(LBound(Vars))
For Counter = LBound(Vars) + 1 To UBound(Vars)
If Vars(Counter) < Result Then Result = Vars(Counter)
Next
Case "MAX"
Result = Vars(LBound(Vars))
For Counter = LBound(Vars) + 1 To UBound(Vars)
If Vars(Counter) > Result Then Result = Vars(Counter)
Next
Case "AVG"
For Counter = LBound(Vars) To UBound(Vars)
If IsNumeric(Vars(Counter)) Then
Numerator = Numerator + Vars(Counter)
Denominator = Denominator + 1
End If
Next
Result = Numerator / Denominator
End Select
RowStats = Result
End Function
Function RowStats(Stat As String, ParamArray Vars())
Dim Numerator As Double
Dim Denominator As Double
Dim Counter As Long
Dim Result
Stat = UCase(Stat)
Select Case Stat
Case "MIN"
Result = Vars(LBound(Vars))
For Counter = LBound(Vars) + 1 To UBound(Vars)
If Vars(Counter) < Result Then Result = Vars(Counter)
Next
Case "MAX"
Result = Vars(LBound(Vars))
For Counter = LBound(Vars) + 1 To UBound(Vars)
If Vars(Counter) > Result Then Result = Vars(Counter)
Next
Case "AVG"
For Counter = LBound(Vars) To UBound(Vars)
If IsNumeric(Vars(Counter)) Then
Numerator = Numerator + Vars(Counter)
Denominator = Denominator + 1
End If
Next
Result = Numerator / Denominator
Case "STDEV", "STDEVP", "VAR", "VARP"
For Counter = LBound(Vars) To UBound(Vars)
If IsNumeric(Vars(Counter)) Then
Numerator = Numerator + Vars(Counter)
Denominator = Denominator + 1
End If
Next
For Counter = LBound(Vars) To UBound(Vars)
If IsNumeric(Vars(Counter)) Then Result = Result + (Vars(Counter) - (Numerator / Denominator)) ^ 2
Next
Result = Result / IIf(Stat Like "*P", Denominator, Denominator - 1)
If Stat Like "S*" Then Result = Result ^ 0.5
End Select
RowStats = Result
End Function
Function RowStats(Stat As String, ParamArray Vars())
' Function by Patrick Matthews
' This Access UDF calculates various stats for the values passed into the ParamArray. It
' was originally designed for passing several values from a particular row set for
' evaluation; since the values come from the same row, the usual aggregate functions would
' not be appropriate.
' The Stat variable (NOT case sensitive) determines what statistic is calculated:
' "min", "max": minimum or maximum
' "sum", "avg": sum or average, excluding dates or strings
' "var", "stdev": sample variance or standard deviation (excluding dates or strings)
' "varp", "stdevp": sample variance or standard deviation (excluding dates or strings)
' You may pass any value in the ParamArray. Strings, nulls, and dates are ignored for the
' sum, avg, stdev, stdevp, var, and varp calculations. (Thus, you could use this function to
' add several columns together without wrapping each column with Nz() to handle nulls...)
Dim Numerator As Double
Dim Denominator As Double
Dim Counter As Long
Dim Result
' Force to upper case to make sure string comparisons are always performed as expected;
' Access uses Option Compare Database by default, but other VBA/VB6 uses binary default
Stat = UCase(Stat)
Select Case Stat
Case "MIN"
' Set the result to the first value in the array, then check the other values in
' turn to see if it is less.
Result = Vars(LBound(Vars))
For Counter = LBound(Vars) + 1 To UBound(Vars)
If IsNull(Result) And Not IsNull(Vars(Counter)) Then
Result = Vars(Counter)
ElseIf Vars(Counter) < Result Then
Result = Vars(Counter)
End If
Next
Case "MAX"
' Set the result to the first value in the array, then check the other values in
' turn to see if it is greater.
Result = Vars(LBound(Vars))
For Counter = LBound(Vars) + 1 To UBound(Vars)
If IsNull(Result) And Not IsNull(Vars(Counter)) Then
Result = Vars(Counter)
ElseIf Vars(Counter) > Result Then
Result = Vars(Counter)
End If
Next
Case "AVG", "SUM"
' Check each value in turn. If it is numeric, then increment numerator and denominator.
' Divide numerator by denominator to get an average, or by 1 to get the sum
For Counter = LBound(Vars) To UBound(Vars)
If IsNumeric(Vars(Counter)) Then
Numerator = Numerator + Vars(Counter)
Denominator = Denominator + 1
End If
Next
Result = Numerator / IIf(Stat = "AVG", Denominator, 1)
Case "STDEV", "STDEVP", "VAR", "VARP"
' Take one pass through the set to determine the average, and then determine the
' sum of squared deviances from the mean. Divide by number of elements in the
' array for population or (elements - 1) for sample. If standard deviation,
' take square root
For Counter = LBound(Vars) To UBound(Vars)
If IsNumeric(Vars(Counter)) Then
Numerator = Numerator + Vars(Counter)
Denominator = Denominator + 1
End If
Next
For Counter = LBound(Vars) To UBound(Vars)
If IsNumeric(Vars(Counter)) Then Result = Result + (Vars(Counter) - (Numerator / Denominator)) ^ 2
Next
Result = Result / IIf(Stat Like "*P", Denominator, Denominator - 1)
If Stat Like "S*" Then Result = Result ^ 0.5
End Select
RowStats = Result
End Function
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
SQL: launch actions one before the other | 10 | 23 | |
ACCESS 2010 - VBA - generic Table Open Button - Code | 7 | 23 | |
Query group by data in SQL Server - cursor? | 3 | 31 | |
Use .MoveNext in VBA but have it filter criteria | 2 | 12 |
Join the community of 500,000 technology professionals and ask your questions.