Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
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
1. Iif - you'll need to nest these.
or
2. If you are ok with writing VBA you can use a VBA function in a module, something like:
function MaxDate(date1 as date, date2 as date, date3 as date, date4 as date)
dim result as date
if date1 > date2 then result = date1 else result = date2
if date3 > result then result = date3
if date4 > result then result = date4
maxdate = result
end function
The VBA method is by far the easiest.
HTH