smallest ( Min ), largest ( Max ), average ( Average ) and standard deviation (
StdDeviation) for all of the numbers in the list, then the following functions
will get you your answers.
You must pass in a list of numbers as Src1 to each function you will use. Each
Function will call the "ConvertToNumberArray" function and perform the
calculations to determine the correct answer.
Function ConvertToNumberArray(Src1 As Variant) As Variant
S1 = SetArray(Src1)
Dim R1
ub1 = Ubound(S1)
dt1 = Datatype(S1(0))
If dt1 < V_STRING Then
R1 = S1
Else
Redim R1(ub1)
For i = Lbound(S1) To ub1
R1(i) = Cdbl(S1(i))
Next
End If
ConvertToNumberArray = R1
End Function
Function Max(Src1 As Variant) As Variant
S1 = ConvertToNumberArray(Src1)
max = - 10 ^ 60
Forall numv In S1
If numv > max Then
max = numv
End If
End Forall
Max = max
End Function
Function Min(Src1 As Variant) As Variant
S1 = ConvertToNumberArray(Src1)
min = 10 ^ 60
Forall numv In S1
If numv < min Then
min = numv
End If
End Forall
Min = min
End Function
Function Average(Src1 As Variant) As Variant
S1 = ConvertToNumberArray(Src1)
Forall numv In S1
sum = sum + numv
End Forall
Average = Round((sum / Ubound(S1)), 2)
End Function
Function StdDeviation(Src1 As Variant) As Variant
S1 = ConvertToNumberArray(Src1)
elem = Ubound(S1)
Forall numv In S1
sum = sum + numv
End Forall
mean = Round((sum / elem), 2)
Forall numv In S1
sumSquared = sumSquared + ((numv - mean) ^ 2)
End Forall
StdDeviation = Round(Cdbl(Sqr(sumSquared / ( elem - 1))), 2)
End Function
This was first published in November 2000