Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging fields within same Record (A2K)

    I
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Averaging fields within same Record (A2K)

    Avg only works over records, not over fields. You have to use a custom function. Copy the following code into a standard module:

    Public Function FAvg(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 IsNumeric(FieldList(i)) Then
    dblSum = dblSum + FieldList(i)
    Else
    intNullCount = intNullCount + 1
    End If
    Next i
    If intNullCount = intHi - intLo + 1 Then
    FAvg = Null
    Else
    FAvg = dblSum / (intHi - intLo + 1 - intNullCount)
    End If
    End Function

    Use it like this:

    =FAvg([txtField1],[txtField2],[txtField3],[txtField4],[txtField5])

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging fields within same Record (A2K)

    Hans,

    I never fail to be amazed. Thank you muchly,

    Andy
    Cheers,
    Andy

Posting Permissions

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