Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Your former advice is way better my friend.
    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
  •