Wrong Average in MS Access Reports

Question

About the problem

I have a report in MS Access that calculates an average:

===== report sample===========

Month1 Month2 Month3 YTD Average

Location 1 34 119 50 203

Location 2 Null Null Null

Location 3 26 36 62

---

---

---

Average 30 78 50 133

(result from MS Access; The average is placed in one of the group sections)

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

I believe that the average results are wrong.  In Month1 the answer should be 10 = [(34+26)/3].

Also the YTD Average shows total and not the average.

The Record Source is a Crosstab Query.

Solution that I am looking for

1- correct monthly average

2- correct YTD monthly average

See the database for more info

---

The name of the report is "Pipeline Conversion Time - All".  Click the link below to open the database.

http://sdrv.ms/10WoY3n

Thanks in advance.

Alfredo

Answer 1

You're most welcome!

Answer 2

Did not download the database but did notice...

1. I believe the answer for Month1 average should be *20*.
2. The Avg() function will not give you the correct results if there is no value in a field.  So, you would need to to set the default as *0*.
3. I am sure this is why your YTD results are also incorrect.

Answer 3

Thanks Gina for the great help.

Replacing the Null with zeroes solve the problem.

I used IIF function to ensure that zeroes are the result in case there are no values.

Again thanks to you.  I will mark this thread as answered.

Sincerely,

Alfredo

Answer 4

NULL is a funny beast. It is NOT equivalent to zero. It means "This value is unspecified, unknown, unavailable".

So if you have three values in three records, and one of those three is unknown - it could be zero, it could be 42, it could be 312294123, it could be ANYTHING - then the sum of the three is also unknown.

The way Access handles this is to simply throw out the record with the null entirely; so the average of (26, NULL, 34) is quite properly 30, simply ignoring the null and averaging the remaining values.

You can use the NZ() function to convert the NULLs into zero if you want the average to be 20.

Answer 5

Which number are you considering YTD Average - 203 or 133?

How those number derived?

Overall values such as for YTD should be in a footer.

Post the crosstab SQL.