How to calculate average without counting missing values in a pivot table?

Hi,

I have a problem when Im trying to calculate averages in my pivot table. I dont know how to handle my missing values. If I leave them blank I cant get the average at all because the values end up under the dates instead of in a column next to the dates where I want them to be. If I write 0 it uses it as a value, eg 2+2+0 and i get 1,33 instead of 2. Also the zero could be a value for some of the parameters, so itīs no good.

I am new to pivot tables, can somebody please help?

Anna

please attach sample x'le file to understand it better.

Do you need to put in a 0 or can you just leave the cell blank. if the information is coming in from another sheet or cell (say zz99) you could use

=if(zz99=0,"",zz99) that way it will return an empty cell for the average not to look at it.

Here it is:

The thing is I dont want to put zeros instead of blanks because it messes up my data. But if I leave it blank I cant manage to make the table in columns because all values end up in one single column.

Is there really no way of creating a pivot table with blanks (or something else than zeros as missing values)? OR if i cant use pivot tables, can I make a formula that allows me to calculate the mean only for 0-10 m of depth each sampling occation?