Melissa Partyka, research ecologist at the Western Center for Food Safety at the University of California-Davis, has provided simple instructions for calculating your geometric mean below.
Geometric Mean (GM) is the average of your log-transformed results.
While any base can be used, most growers use log base 10 to perform their transformations. Numbers can be transformed using a calculator, or in a spreadsheet which allows for multiple transformations at the same time.
In a calculator, type in your test result and press the ‘log’ button. If you type in 10 and press ‘log’, you should see ‘1’, if you type 100 and press ‘log’ you should see ‘2’. If you add up all of these transformed values and then divide them by the total number of values, you’ll have your GM. So 1+2=3, 3/2=1.5.
If you want to see what this number means in nonlog form, simply type in your result, here 1.5, and press ‘10x’ on your calculator. You’ll get 31.6. Both 1.5 and 31.6 are your GM, they’re just different ways of saying the same thing.
In Excel, there are a couple of formulas that you’ll need. LOG10 and AVERAGE.
Enter your data in a single column, e.g. column A. In column B type “=LOG10(A1). Hit Return. Either copy/paste this formula next to the rest of your data or drag the formula down.
In column C type “=Average(B1:B#), hit return. If you have 5 samples B# will be B5, if you have 20 samples B# will be B20, etc. To back transform, in column D type “=10^C1”. Hit return. The numbers in column C and column D are both your GM.
Your Statistical Threshold Value (STV)is found when your data are plotted on a “log-normal” distribution. This is not easy to do by hand, but can be done easily in Excel using a couple of other formulas.
Assuming you still have your log-transformed data in column B and your log-transformed GM in column C, type into column E “=STDEV.S(B1:B#).
This is the standard deviation of your samples. The next step is the trickiest, but you’ll get your result. In column F type “=(E1*1.282)+(C1). Again, to back transform, take this value and raise 10 to it, type =10^F1. Both of these numbers are your STV.