Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging (2000)

    I have an access report that does an average of six results. However, I want the average to NOT include any result of zero, how would I do this. This is the formula I use.

    =Avg([CS 1-2 INCH])

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging (2000)

    You can't do it with the built in Avg function, you have to count the number of results <> 0 and then divide the sum by that number to get the average.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging (2000)

    I just tried this simple code in the on gotfocus event, you can use it in other ways.

    Dim strVar As String
    Dim strOutput As String

    strVar = 0

    If Me.Text0 <> 0 Then
    strVar = strVar + 1
    End If
    If Me.Text2 <> 0 Then
    strVar = strVar + 1
    End If
    If Me.Text4 <> 0 Then
    strVar = strVar + 1
    End If
    If Me.Text6 <> 0 Then
    strVar = strVar + 1
    End If

    strOutput = (Me.Text0.Value + Me.Text2.Value + Me.Text4.Value + Me.Text6.Value) / strVar

    Me.Text8 = strOutput


    The text boxes just hold values, text8 give the average when it gets the focus.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging (2000)

    Why are you using a string variable for addition? There is implicit coercion in VBA but it's bad practice. And I would suggest you add handling for nulls, because using a string variable instead of a numeric variable and using the + operator will return a null string if any of the individual elements are null.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging (2000)

    I am having a problem with Syntax. How do I count and exclude 0's?
    Thanks in advance,
    Kristen

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Averaging (2000)

    Here is an example of a user-defined function that can be used for this purpose:

    <code>Public Function GetAvg(ByRef bExcludeZero As Boolean, ParamArray Args()) As Variant</code>

    <code> Dim lngArgCount As Long</code>
    <code> Dim n As Long</code>
    <code> Dim dblSum As Double</code>
    <code> Dim lngDiv As Long</code>

    <code> lngArgCount = UBound(Args) + 1</code>
    <code> lngDiv = lngArgCount</code>

    <code> For n = 0 To lngArgCount - 1</code>
    <code> ' Exclude Nulls from Avg:</code>
    <code> If IsNull(Args(n)) Then</code>
    <code> lngDiv = lngDiv - 1</code>
    <code> Else</code>
    <code> dblSum = dblSum + Args(n)</code>
    <code> ' Exclude zero's if bExcludeZero is True:</code>
    <code> If bExcludeZero And (Args(n) = 0) Then</code>
    <code> lngDiv = lngDiv - 1</code>
    <code> End If</code>
    <code> End If</code>
    <code> Next n</code>

    <code> If lngDiv > 0 Then</code>
    <code> GetAvg = dblSum / lngDiv</code>
    <code> Else</code>
    <code> ' Function declared as Variant in case Null should be returned</code>
    <code> ' Else return zero if all args are Null or Zero</code>
    <code> GetAvg = 0</code>
    <code> End If</code>

    <code>End Function</code>

    Test results:

    <code>? GetAvg(True,1,2,3,4,5,0,0)</code>
    <code> 3 </code>
    <code>? GetAvg(False,1,2,3,4,5,0,0)</code>
    <code> 2.14285714285714 </code>
    <code>? GetAvg(True,1,2,3,4,5,0,Null)</code>
    <code> 3 </code>
    <code>? GetAvg(False,1,2,3,4,5,0,Null)</code>
    <code> 2.5 </code>
    <code>? GetAvg(False,0,0,0)</code>
    <code> 0 </code>

    Note - normally when computing an average, you want to exclude Nulls, but include zero values. Above function was modified to include option of excluding zero values. Test results show how this option affects return value. Note use of ParamArray keyword, which allows you to pass function an arbitrary number of arguments as an array of Variants. ParamArray must always be last argument in function's arg list. If for some reason you did NOT want to exclude Null values (nor exclude zeroes) from the calculation, pass values to function using NZ function, which will return zero for Null values, and set bExcludeZero to False.

    HTH

Posting Permissions

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