D
DonD
I need to create a function that will calculate an appropriate mean and
variance when there are "missing" data. Let me explain with a fisheries
sampling example. I have two tables with a one to many relationship.
Table 1 is the sampling event table. It records the type of sample, the
sampling station, replicate #, date of sample, etc.
Table 2 is the child and contains the information of what I caught in the
sample (spp, length, weight, etc)
Lets say I have four replicates for sample 1 and that I catch the following
numbers of catfish in each rep: 5, 0, 3, 0. Obviously the mean would be 2
(8/4), but heres the kicker. For the reps where I didn't catch any catfish
(0's), I do not have a record for them. Likewise for the other 40 spp; we
only record what we catch.
Now, for an individual by individual species basis, I think I can do an
outer join (e.g. include all records from table 1) with the Nz function to
generate the zeros; However, I'm trying to figure out a way to do this "on
the fly" in a Pivot chart so I can look at mean catch rates of multiple
species simultaneously.
Any guidance or pointers in the right direction would be greatly appreciated.
DonD
variance when there are "missing" data. Let me explain with a fisheries
sampling example. I have two tables with a one to many relationship.
Table 1 is the sampling event table. It records the type of sample, the
sampling station, replicate #, date of sample, etc.
Table 2 is the child and contains the information of what I caught in the
sample (spp, length, weight, etc)
Lets say I have four replicates for sample 1 and that I catch the following
numbers of catfish in each rep: 5, 0, 3, 0. Obviously the mean would be 2
(8/4), but heres the kicker. For the reps where I didn't catch any catfish
(0's), I do not have a record for them. Likewise for the other 40 spp; we
only record what we catch.
Now, for an individual by individual species basis, I think I can do an
outer join (e.g. include all records from table 1) with the Nz function to
generate the zeros; However, I'm trying to figure out a way to do this "on
the fly" in a Pivot chart so I can look at mean catch rates of multiple
species simultaneously.
Any guidance or pointers in the right direction would be greatly appreciated.
DonD