Tip

Find The Min, Max, Average And Standard Deviation For A Set Of Numbers.

If you have a list of numbers ( text or numbers ) and you want to find the
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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.