# Thread: Calculating average across Text Boxes in a Form (2003)

1. ## Calculating average across Text Boxes in a Form (2003)

In the Media text box I'm trying to calculate the average of the content of the 15, 30, 45 and 60 text boxes of the startup form in the attached mdb.
The formula I use doesn't work if, e.g., there's a Null in the 15 text box.
How do you calculate the average in these cases?

2. ## Re: Calculating average across Text Boxes in a Form (2003)

You can use the FAvg function from <post:=440,550>post 440,550</post:>. The control source for the Media text box becomes

=FAvg([15],[30],[45],[60])

3. ## Re: Calculating average across Text Boxes in a Form (2003)

Thank you Hans, that code for the FAvg function is mind-boggling <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.
The TESTO123 text box in the startup form is supposed to tell the user what value to put in the empty cells of a row so that the MEDIA text box value is equal to the value of the TARGET text box. I've tried using
<font color=448800>=IIf(IsNull([15]) Or [15]=0,[target],IIf(IsNull([30]) Or [30]=0,([target]*4-[15])/3,IIf(IsNull([45]) Or [45]=0,([target]*4-[15]-[30])/2,IIf(IsNull([60]) Or [60]=0,([target]*4-[15]-[30]-[45]))))) </font color=448800>
as control source for the TESTO123 text box but you can see that, e.g., putting 13472 in the 45 text box for Jan 13, 2006 for the row with 10 in the ORA text box, doesn't set MEDIA to 13500.

4. ## Re: Calculating average across Text Boxes in a Form (2003)

To do this correctly, you'd have to take all combinations of blank and non-blank cells into account. This is hard to do in an expression, I'd write a VBA function for it.
- Count the number of blanks in [15], [30], [45] and [60].
- If this is 0, get out.
- Subtract the sum of the filled-in values from 4*Target.
- Divide the result by the number of blanks.

5. ## Re: Calculating average across Text Boxes in a Form (2003)

This cumbersome expression may also do what you want:

=IIf(IsNull([15])+IsNull([30])+IsNull([45])+IsNull([60])=0,Null,(Nz([15],0)+Nz([30],0)+Nz([45],0)+Nz([60],0)-4*[Target])/(IsNull([15])+IsNull([30])+IsNull([45])+IsNull([60])))

6. ## Re: Calculating average across Text Boxes in a Form (2003)

Fantastic, I'm speechless <img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15>
You're an <img src=/S/artist.gif border=0 alt=artist width=34 height=29> Hans

7. ## Re: Calculating average across Text Boxes in a Form (2003)

Public Function FAvgX(target, ParamArray FieldList() As Variant) As Variant
Dim i As Integer
Dim intLo As Integer
Dim intHi As Integer
Dim intNullCount As Integer
Dim dblSum As Double

intLo = LBound(FieldList)
intHi = UBound(FieldList)
For i = intLo To intHi
If IsNull(FieldList(i)) Then
intNullCount = intNullCount + 1
Else
dblSum = dblSum + FieldList(i)
End If
Next i
If intNullCount = 0 Then Exit Function
FAvgX = (4 * target - dblSum) / intNullCount

End Function

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•