Average Calculation when zeroes are present

[KarenB]

I am trying to do a calculation to get an average of the number data in 5
fields on a report. However, there is not always data in all 5 fields, so the
average will sometimes use 3 of them, 4 of them, etc. I have no trouble
getting the correct result when all 5 fields are greater than zero, but I
can't figure out how to ignore the zeroes (I converted the null values in
those fields to zero so the calculation would work, but I'm stumped on how to
do the average on a variable number of fields). Help!

 

[Allen Browne]

The real problem here is that you have repeating fields. In a relational
database, these values should be in one column, in a related table, so that
one of these records can have many values in the related table. It would
then be a really simple matter to average them.

If you don't want to do that, you will be writing some crazy, convoluted
expressions like this:

=IIf([A] Is Null AND Is Null AND [C] Is Null AND [D] Is Null AND [E] Is
Null, Null,
(Nz([A],0) + Nz(,0) + Nz([C],0) + Nz([D],0) + Nz([E],0)) /
-(([A] Is Not Null) + ( Is Not Null) + ([C] Is Not Null) + ([D] Is Not
Null) + ([E] Is Not Null)))

Substitute yoru field names for A - E.

 

[Jeff Boyce]

Karen

If you have "five fields" with numbers in some/all, you have ... a
spreadsheet! Have you considered exporting the data to excel, then using a
couple functions (Sum() & Count()) to compute "average"?

There are multiple problems (in Access) with doing what you described.

First, changing nulls to zeros doesn't help, since "0" is a number, too.
You could average 0,1,2 and get "1".

Also, Access' aggregation functions (e.g., Avg()) work DOWN, not across.
Across is what you do with spreadsheets.

If you absolutely, positively must work in Access, consider converting your
existing table structure to one that is well-normalized (more down than
across). That way, the Access Avg() function will automatically only
include the non-null values (hint, send those 0's back to null, unless, of
course, your numbers also include REAL zeros?!).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

Hi Karen,

Paste the following code into a module.

===================================================

Public Const strError As String = "Error"

Public Function AverageAny(ParamArray var() As Variant) As Variant

' Average function that accepts any number of parameters.

' Author: Clifford Bass

' Parameters: Any number accepted, but only numeric values will be used
' Returns: The average of all the numerical values, if there are any,
otherwise Null

Dim i As Integer
Dim intCount As Integer
Dim dblSumValues As Double

On Error GoTo AverageAny_Error

intCount = 0
dblSumValues = 0
For i = LBound(var()) To UBound(var()) Step 1
SumAndCount var(i), dblSumValues, intCount
Next i
If intCount > 0 Then
AverageAny = dblSumValues / intCount
Else
AverageAny = Null
End If

AverageAny_Exit:
Exit Function

AverageAny_Error:
AverageAny = strError
Resume AverageAny_Exit

End Function

Private Sub SumAndCount(ByVal varItem As Variant, ByRef dblSumValues As
Double, _
ByRef intCount As Integer)

' Helper routine for AverageAny

Dim i As Integer

If Not IsNull(varItem) Then
If IsArray(varItem) Then
For i = LBound(varItem) To UBound(varItem) Step 1
SumAndCount varItem(i), dblSumValues, intCount
Next i
Else
If IsNumeric(varItem) Then
dblSumValues = dblSumValues + varItem
intCount = intCount + 1
End If
End If
End If

End Sub

===================================================

Use it as follows:

Private Sub SomeSub()

Dim varResult As Variant

varResult = AverageAny(Field1, Field2, Field3, Field4, Field5)

If IsNull(varResult) Then
' No values found
.....
Else
If varResult = "Error" Then
' An error occurred
....
Else
' An average has been calculated successfully
........
End If
End If

End Sub

In theory you can specify an unlimited number of fields. The only
exception is if you are using it directly in a query, in which case, if I
recall corrrectly, you are limited to 29. When you are calling it from code
you can actually pass multiple arrays of values in as the parameters.

Hope this helps,

Clifford Bass

 

[KarenB]

Thanks everyone for your help. I learned enough from each of you to muddle
through and get this to work - I appreciate your input!

 

[Clif McIrvin]

message

In theory you can specify an unlimited number of fields. The only
exception is if you are using it directly in a query, in which case,
if I
recall corrrectly, you are limited to 29. When you are calling it
from code
you can actually pass multiple arrays of values in as the parameters.

Hope this helps,

Clifford Bass

Clifford, I like your thinking! Thanks for sharing; this one is going
into my useful examples folder, for sure and certain!

 

[Clifford Bass]

Hi Clif,

You are welcome! I wrote a number of other similar functions that do
medians, maximums, minimums, standard deviations, modes, counts and sums.
They use essentially all the same/similar concepts as you see in the
AverageAny function.

Clifford Bass