Average Maximum and Minimum from 6 text boxes

Question

Post reply

Average Maximum and Minimum from 6 text boxes

Cory McRae

Say Hey Kid

Points: 664
More actions
August 7, 2006 at 4:34 pm

#169908

Average Maximum and Minimum from 6 text boxes

A form where there is automaticly inserted 6 width demntions in a float data type. I only need to store in the table the Average Maximum and Minimum of these numbers.

I am using vba to get the Average

Private Sub ThickAvg_GotFocus()
Me.ThickAvg = (Me.Thick1 + Me.Thick2 + Me.Thick3 + Me.Thick4 + Me.Thick5 + Me.Thick6) / 6
End Sub

I do not know how to get the Maximum and Minimum.

I think a better way would be to put thick1-6 in an array and then use a vba function but I have not found a function to use.

Please help

ersonName>Cory McRaeersonName>

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

if you put all 6 in a array, you can use a sorting algorithm to order them from low to high.

http://en.wikipedia.org/wiki/Sorting_algorithm

Average maximum=average of the 3 highest numbers, average minimum=average 3 lowest numbers?

Stephen Turner Mr or Mrs. 500 Points: 540 More actions · Answer 2

Here's a little function that will find the minimum...

Public Function Min(ParamArray Numbers())
Dim i As Integer
    i = LBound(Numbers)
    Min = Numbers(i)
    i = i + 1
    Do While i <= UBound(Numbers)
        Min = IIf(Min < Numbers(i), Min, Numbers(i))
        i = i + 1
    Loop
End Function

Swap min to max and < to > for a max function. Just call it with:

Me!ThickMin = Min(Me!Thick1, Me!Thick2, Me!Thick3, Me!Thick4, Me!Thick5, Me!Thick6)

Note also you should use ! for members of a collection and . for properties and methods of an object.

Cory McRae Say Hey Kid Points: 664 More actions · Answer 3

Thank you,

For your suggestions I am very new to VBA I will try both over the next day or two and post what I end up using

Cory McRae

Cory McRae Say Hey Kid Points: 664 More actions · Answer 4

This is what I decided to use

Dim Min As Double

Min = Me.Thick1

Min = IIf(Min > Me.Thick2, Me.Thick2, Min)

Min = IIf(Min > Me.Thick3, Me.Thick3, Min)

Min = IIf(Min > Me.Thick4, Me.Thick4, Min)

Min = IIf(Min > Me.Thick5, Me.Thick5, Min)

Min = IIf(Min > Me.Thick6, Me.Thick6, Min)

Me.ThickMin = Min

Thanks for all of your help this group is great