Average based on number of data points (1 Viewer)

[accessaspire219]

I have table which contains 6 month historical usage of items. I am trying to calcuate an average usage number for each item. The issue is that not all items have 6 months of data. So for an item that has 3 months of data I want to calculate the average such that the denominator is 3 and not 6. The caveat is an item can have 6 months of data but months 1 and 3 could be 0 but the average will still be calculated using a denominator of 6.
For example: Item A sold 1, 2, 3,4,5,6 in months 1 (most recent) to 6 (6 months ago) --> Denominator should be 6
Item B --> 1,0,3,0,5,6 --> Denominator should be 6
Item C --> 0,0,0,1,2,3 --> Average will have a denominator of 3.

If I use the average function in access it will use a denominator of 6 for all items since every item will have 6 columns.

Does any one know how can achieve what I explained in the example above?

Any help is appreciated!
Thanks!!

 

[Galaxiom]

Change the value in the records you don't want included from zero to Null. Average ignores Nulls.