Averaging in a Report

[MichelleM via AccessMonster.com]

I have a report created that lists one agent's data per page. Each agent
sees their data (calls, time, etc.) plus they also see their team's average.
If an agent has all 0's in their data fields (they were on vacation during
the timeframe of the report) is there a way for Access to not include those
zeros in the average?

 

[Allen Browne]

Whereever your source data comes from, you need Null instead of zero.

Null (left completely blank) means the value doesn't exist, is unknown, or
not applicable. Therefore Access ignores nulls when it calculates the
average. Zero means the number is known, and so it does skew the average, as
you found.

So, store the data or design the source query so you have Null rather than
zero for the values that don't apply.

 

[MichelleM via AccessMonster.com]

That worked! Thank you!

Whereever your source data comes from, you need Null instead of zero.

Null (left completely blank) means the value doesn't exist, is unknown, or
not applicable. Therefore Access ignores nulls when it calculates the
average. Zero means the number is known, and so it does skew the average, as
you found.

So, store the data or design the source query so you have Null rather than
zero for the values that don't apply.

I have a report created that lists one agent's data per page. Each agent
sees their data (calls, time, etc.) plus they also see their team's

[quoted text clipped - 3 lines]

those
zeros in the average?