Pivot table does not average with zeros

Question

Hi-

I have a pivot table that is (in theory) going to produce average density values for data that are sorted by date/site/haul number  (these are fish data) on the vertical axis.  The data are coming from Access; the issue is that there are varying numbers of hauls at each site, and if zero fish are caught, that shows up as a blank, not a zero (data going into the pivot are from calculations, not straight counts, and changing the database format would be a huge project).  I changed the options to fill a zero in each blank cell, but of course that is after-the-fact and it does not incorporate the zero in the average.  (see column for chum salmon, at "Chehalis R restrtn" there were two hauls, but it returns an average of 23.727... instead of averaging with zero to get 11.86). 

Is there any way around this?  I tried copying the data from the pivot table into a new worksheet, but then the first column (containing date, site, and haul) are all jumbled together and the corresponding data is also difficult to tease apart (wksht has about 1700 rows of data in it).

Any help would be greatly appreciated!

Thanks.

Average of FishPer Hectare	Column Labels
Row Labels	Bull Trout	Chum Salmon	Cutthroat Trout	Steelhead_AdClip
3/16/2011	0	59.31889343	0	0
Wishkah R Slough	0	59.31889343	0	0
2	0	47.45511627	0	0
3	0	71.18267059	0	0
3/17/2011	0	296.5944614	0	0
Johns R channel	0	296.5944614	0	0
1	0	569.4613647	0	0
2	0	23.72755814	0	0
3/23/2011	0	344.0495758	0	0
Chehalis SP1	0	118.6377869	0	0
1	0	118.6377869	0	0
Chehalis SP2	0	569.4613647	0	0
1	0	569.4613647	0	0
3/24/2011	0	599.5953369	0	0
W Fk Hoquiam R	0	599.5953369	0	0
1	0	599.5953369	0	0
3/30/2011	0	3440.49585	0	0
Beardslee Slough	0	3440.49585	0	0
1	0	3440.49585	0	0
3/31/2011	0	1160.971186	71.18267059	0
Chehalis R restrtn	0	23.72755814	0	0
1	0	0	0	0
2	0	23.72755814	0	0
Damon Pt	0	7280.805176	0	0
1	0	7280.805176	0	0

Answer 1

Hi,

I don't think you can do anything about this in MS Excel.  If at all there is a solution, it would exist in MS Access only.

Alternatively, instead of getting data in MS Excel directly inside a Pivot Table, get data in MS Excel in a Table first.  You can then Copy the Table and Paste ii Special as Values.  Apply a filter on the numeric column on Blanks and put 0's there.

Now create a Pivot Table.

Answer 2

Hi,

Welcome to Microsoft Community.

Since your issue involves Pivot table and the data being taken from Access database, it would be best if you could post your question in Excel IT Pro forum for better suggestions:

http://social.technet.microsoft.com/Forums/en/excel/threads

Thank you.