# Thread: Averaging (2000)

1. ## 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. ## 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.

3. ## 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. ## 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.

5. ## Re: Averaging (2000)

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

6. ## 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
•