Since a geometric mean is the anti-log of the sum of the logs divided by the number of samples and the log of zero (0) is not defined, three workarounds are commonly used. One must check with their local regulatory agency for the proper procedure as they vary regionally throughout the US.
- If any value is zero (0), one is added to each value in the set and then one is subtracted from the result.
- Blank and 0 values are ignored in the calculation.
- Zero (0) values are converted to one (1) for the calculation.
For example: The actual readings are 100, 50, 0, 25, 0, 60. Method 1 would convert them to 101, 51, 1, 26,1, 61 and their logs are 2.0043, 1.7076, 0, 1.4150, 0, 1.7853; the sum is 6.9122 divided by 6 = 1.15203. The antilog is 14.14.1916 minus 1 = 13.1916.
Method 2 would convert them to 100, 50, BLANK, 25, BLANK, 60 and their logs are 2, 1.6990, BLANK, 1.3979, BLANK, 1.7782; the sum is 6.8751 divided by 4 = 1.7188. The antilog is 52.3317.
Method 3 would convert them to 100, 50, 1, 25, 1, 60 and their logs are 2, 1.6990, 0, 1.3979, 0, 1.7782; the sum is 6.8751 divided by 6 = 1.1458. The antilog is 13.9909.
In summary, the three methods return three different answers; 13.1916, 52.3317 and 13.9909.
These methods either require invalid entries or the spread sheets must be modified when the calculation is made. Programs exist where the data is entered correctly and the proper rule is invoked when the calculation is made.
