Calculating an average score 2

[VillaRestal]

Is it possible to calculate the average of values entered into a form and display the result on that form?

I have a short form that asks for scores from 6 judges.

I need to be able to take the average of the scores given, the number of judges can be from 2 to 6, and display it on the form. I would also like to be able to take that result and "adjust" it by multiplying it by a small number.

Any help would be appreciated, and if its a matter of directing me to where the information is located all the better.

Thanks in advance.

 

[THWatson]

VillaRestal
I'm sure there are other ways...but here is one, and maybe it will get you started.

Your form has 6 text boxes for the Judges's scores. I have called them Text1 through Text6. Create two additional controls. Call the first one, with the IIf structures something like "txtSumBoxes".

=IIf(IsNull([Text1]),0,1)+IIf(IsNull([Text2]),0,1)+IIf(IsNull([Text3]),0,1)+IIf(IsNull([Text4]),0,1)+IIf(IsNull([Text5]),0,1)+IIf(IsNull([Text6]),0,1)

This txtSumBoxes takes a count of the score boxes that have values in them, 1 if a box has a score, 0 if not.

The second text box checkes each box for Nulls, sums the scores and then divides by the value in txtSumBoxes.

=(Nz([Text1])+Nz([Text2])+Nz([Text3])+Nz([Text4])+Nz([Text5])+Nz([Text6]))/[txtSumBoxes]

As I say, there will be other ways...such as doing a structure in VBA code...but this gives you essentially what you want.

Tom

 

[willir]

Tom - I love your solution!

VillaRestal

Two other approaches...
If the data is written to the table, use the DAvg function. It is by far the simplest solution.

=Nz(DAvg("[YourScoreField]", "YourTable], "[YourKeyField] = " & Me.KeyFieldOnForm))

The VBA solution would be similar to that presented by Tom but by using EventProcedures

 

[VillaRestal]

Thanks for the responses guys. I will try these out. Thanks again.

 

[MichaelRed]

Public Function basAvgOfSet(ParamArray MySet() As Variant) As Single

    'Michael Red    8/27/02
    'Sample Usage:  ? basAvgOfSet(1, 2, 3, ,4, 5)
    '               3

    Dim Idx As Integer
    Dim MyCnt As Integer
    Dim MySum As Double

    While Idx <= UBound(MySet)
        If (Not IsMissing(MySet(Idx))) Then
            MyCnt = MyCnt + 1
            MySum = MySum + MySet(Idx)
        End If
        Idx = Idx + 1
    Wend

    If (MyCnt > 0) Then
        basAvgOfSet = MySum / MyCnt
    End If

End Function/





[code]

MichaelRed
mlred@verizon.net