Averages that disregard zeroes.

R

Rockintoddo

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?
 
A

Arvin Meyer [MVP]

Rockintoddo said:
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.
 
P

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

Arvin Meyer said:
Rockintoddo said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top