Pivot Table Calculations
 

Greetings,

I have a very large database that I am trying to perform some calculation on using Excel. I need to calculate geometric means on subsets of the data (specifically, I need to calculate geomean for several fields for each "site" within the dataset over a specific period of time). I think that using pivot tables is the way to go, but I can't quite figure it out. This is what my database spreadsheet looks like:

site date a b c d etc.
1 6/1/06
6/2/06
6/3/06
etc.
2
3
etc.

where a, b, c, d, etc. are all my fields (there are about 100 of them). Each site has been sampled on multiple dates. For most of the fields I want to calculate the mean (average) for each site, which I can do quite easily in a pivot table. Four of the fields need to be calculated as geometric means, however. When I set up my pivot table it looks like this:

site data total
1 mean a #
mean b #
mean c #
etc.
2
3
etc.

Is there a way to calculate the geomean? I tried using a calculated field but I think you can only sum the values, which does not seem to help (at least, I seem to come up with sums when I try it). In my searching of the forums I get the sense that maybe a helper column is the way to go, but I can't wrap my head around how to do it. Can anyone help?

Also, within the database there are quite a few cells that contain "no data" - this happens when sampling occurs on a given date but not at a given site. Are these blank cells being included in the mean calculations, or not? I definitely don't want them to be.

Thanks very much!

Carrie