Calculating Geometric Mean in a Pivot Table

[Carrie]

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




--
Carrie

[addeskins]

Since the Geomean is the nth root of the Product of the values, generate a pivot table with a column for the Number of values using the "Count" function (Column B) and a column for the Product of Values (Column C) for each Site (Column A). Then, Add a Column to the right of the pivot table bringing the two together with "=IF(ISBLANK(A4),"",C4^(1/B4))"

Good Luck