Averaging (2000)

Topic

New Reply

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])

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Public Function GetAvg(ByRef bExcludeZero As Boolean, ParamArray Args()) As Variant

Dim lngArgCount As Long
Dim n As Long
Dim dblSum As Double
Dim lngDiv As Long

lngArgCount = UBound(Args) + 1
lngDiv = lngArgCount

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

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

End Function

Test results:

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

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

Reply | Quote

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

Reply | Quote