averages in pivot table

[TomS]

I created a pivot table to show data over a 12 month time
frame. I am trying to get the monthly average. However,
it does not include months with null values in the
average. For example the value for Feb is 60 and March is
60 and all other months are null for a total of 120 for
the year. The monthly average for the whole year should
be 10. However the pivot table will give an average of
60.

How do I get the pivot table to include null values in the
calculation of the average?

 

[Peo Sjoblom]

You have to replace blanks/null with 0, that way you'll get 10..

 

[TomS]

It did not work. In pivot table options I selected
replace nulls with 0 and it still did not include the
zeroes in calculating the average

 

[Peo Sjoblom]

That is not what I meant. Select the original range that you are using for
the pivot table, press F5, special select blanks
type 0 and press Ctrl + Enter. Now refresh the pivot table

 

[TomS]

That will not work because the original range does not
have the blanks.

Unit Name BPD Category Year of Date Discovered
Month of Date Discovered
New York-Penn Region - ARC BC-40 2003 2-Feb
New York-Penn Region - ARC BC-41 2003 2-Feb
New York-Penn Region - ARC BC-42 2003 2-Feb
New York-Penn Region - ARC BC-43 2003 2-Feb
New York-Penn Region - ARC BC-44 2003 2-Feb
New York-Penn Region - ARC CP-50 2003 2-Feb
New York-Penn Region - ARC CP-51 2003 2-Feb
New York-Penn Region - ARC CP-52 2003 2-Feb
New York-Penn Region - ARC DD-30 2003 2-Feb
New York-Penn Region - ARC DD-32 2003 2-Feb
New York-Penn Region - ARC DS-21 2003 2-Feb

The pivot table needs to show 0 for BC-45 but since there
is not data for it, it is not included in the original
data.