C
Carrie
Greetings,
I have a very large database that I am trying to perform som
calculation on using Excel. I need to calculate geometric means o
subsets of the data (specifically, I need to calculate geomean fo
several fields for each "site" within the dataset over a specifi
period of time). I think that using pivot tables is the way to go, bu
I can't quite figure it out. This is what my database spreadsheet look
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
want to calculate the mean (average) for each site, which I can d
quite easily in a pivot table. Four of the fields need to be calculate
as geometric means, however. When I set up my pivot table it looks lik
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 calculate
field but I think you can only sum the values, which does not seem t
help (at least, I seem to come up with sums when I try it). In m
searching of the forums I get the sense that maybe a helper column i
the way to go, but I can't wrap my head around how to do it. Can anyon
help?
Also, within the database there are quite a few cells that contain "n
data" - this happens when sampling occurs on a given date but not at
given site. Are these blank cells being included in the mea
calculations, or not? I definitely don't want them to be.
Thanks very much!
Carri
I have a very large database that I am trying to perform som
calculation on using Excel. I need to calculate geometric means o
subsets of the data (specifically, I need to calculate geomean fo
several fields for each "site" within the dataset over a specifi
period of time). I think that using pivot tables is the way to go, bu
I can't quite figure it out. This is what my database spreadsheet look
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
want to calculate the mean (average) for each site, which I can d
quite easily in a pivot table. Four of the fields need to be calculate
as geometric means, however. When I set up my pivot table it looks lik
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 calculate
field but I think you can only sum the values, which does not seem t
help (at least, I seem to come up with sums when I try it). In m
searching of the forums I get the sense that maybe a helper column i
the way to go, but I can't wrap my head around how to do it. Can anyon
help?
Also, within the database there are quite a few cells that contain "n
data" - this happens when sampling occurs on a given date but not at
given site. Are these blank cells being included in the mea
calculations, or not? I definitely don't want them to be.
Thanks very much!
Carri