Calculating a geometric mean
I have a table of water testing results for ten different dates (i.e. 10 records although the number of records involved is not always 10), and need to compute the geometric mean of the SamplingResult (i.e. a number field). To do this I need to multiply the 10 SamplingResult values and then take the 10th root of the product.
PhilModJunk provides a nice solution to this problem when you are trying to take the geometric mean of a set of related records (see Calculating Stock Index Returns).
Specifically, he proposes the following formula:
Evaluate ( Substitute ( List ( ChildTable::Index ) ; ¶ ; " * " ) ) ^ ( 1/ Count ( ChildTable::Index ) )
But because of the list function, it only seems to work with related records. I want to do essentially the same calculation, but directly on the current found set in my table. Am wondering if there is a better way to do this than creating a self-join and then writing a script that sets a key field to make the self-join work for the records of the current found set.
Thanks for any suggestions anyone can offer!