Averages that disregard zeroes.

[Guest]

I am building a report that shows call center data that I need to average.
Some of the people have zeroes in this column, and I want to keep their
information in the report, but disregard the zeroes when calculating the
average. (Did that make any sense?) I need to keep the people with zeroes in
my report, but don't want their zeroes to drag down the average of the other
people. Any ideas?

 

[Arvin Meyer [MVP]]

I am building a report that shows call center data that I need to average.
Some of the people have zeroes in this column, and I want to keep their
information in the report, but disregard the zeroes when calculating the
average. (Did that make any sense?) I need to keep the people with zeroes
in
my report, but don't want their zeroes to drag down the average of the
other
people. Any ideas?

Yes, I posted the answer below at least 10 years ago ... maybe longer. It
was set up to average non-null values, but a little tweaking will get you
what you want:

You can do this but not in a single query (you'll get overflow and/or divide
by zero errors) and you'll need 2 functions similar to Excel CountIf and
SumIf functions:

Public Function CountIf(numIn) As Integer
On Error Resume Next
If IsNumeric(numIn) Then
If numIn > 0 Then
CountIf = 1
Else
CountIf = 0
End If
End If
End Function

Public Function SumIf(numIn) As Double
On Error Resume Next
If IsNumeric(numIn) Then
If numIn > 0 Then
SumIf = numIn
Else
SumIf = 0
End If
End If
End Function

qry1:
SELECT Table1.TestDouble, CountIf([TestDouble]) AS TCount,
SumIf([TestDouble]) AS TSum
FROM Table1;

qry2:
SELECT Sum(qry1.TCount) AS SumOfTCount, Sum(qry1.TSum) AS SumOfTSum
FROM qry1;

qry3:
SELECT [SumOfTSum]/[SumOfTCount] AS Result
FROM qry2;

This will give you the average of all non-null values of TestDouble. It will
error out if you do not have at least one non-zero value in the TestDouble
field.

 

[Paul Shapiro]

The avg aggregate ignores null values. Use an expression replacing the
zeroes with nulls, something like:
AvgAbsentIgnoringZeros: Avg(IIf([daysAbsent]=0,Null,[daysAbsent]))

I am building a report that shows call center data that I need to average.
Some of the people have zeroes in this column, and I want to keep their
information in the report, but disregard the zeroes when calculating the
average. (Did that make any sense?) I need to keep the people with
zeroes in
my report, but don't want their zeroes to drag down the average of the
other
people. Any ideas?

Yes, I posted the answer below at least 10 years ago ... maybe longer. It
was set up to average non-null values, but a little tweaking will get you
what you want:

You can do this but not in a single query (you'll get overflow and/or
divide
by zero errors) and you'll need 2 functions similar to Excel CountIf and
SumIf functions:

Public Function CountIf(numIn) As Integer
On Error Resume Next
If IsNumeric(numIn) Then
If numIn > 0 Then
CountIf = 1
Else
CountIf = 0
End If
End If
End Function

Public Function SumIf(numIn) As Double
On Error Resume Next
If IsNumeric(numIn) Then
If numIn > 0 Then
SumIf = numIn
Else
SumIf = 0
End If
End If
End Function

qry1:
SELECT Table1.TestDouble, CountIf([TestDouble]) AS TCount,
SumIf([TestDouble]) AS TSum
FROM Table1;

qry2:
SELECT Sum(qry1.TCount) AS SumOfTCount, Sum(qry1.TSum) AS SumOfTSum
FROM qry1;

qry3:
SELECT [SumOfTSum]/[SumOfTCount] AS Result
FROM qry2;

This will give you the average of all non-null values of TestDouble. It
will
error out if you do not have at least one non-zero value in the TestDouble
field.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com