Organismic population counts are notorious for their irregular pattern. When plotted on a chart in sequential order, the result is a jagged, apparently chaotic time series. Occasionally there can be an unusually high or low count which has no apparent reason. If the drop is to a level below the survival threshold the population will become extinct. The probability of such an event can be estimated from the distribution of the counts in the record provided that: 1) the fluctuations are random, 2) the series has no trend and 3) the ambient conditions that may affect the counts remain about the same. If these conditions are satisfied, and if the counts' distribution can be assumed to be either normal (symmetric) or lognormal (skewed to the right), at least as an approximation, then the probability of accidental extinction can be estimated by the procedure used in this worksheet, at least theoretically.

Notice that this Excel workbook can also be used to estimate the probability of encountering counts of probiotic mirobes or spores in industrial products that are below specified thresholds. It can also be used to calculate the probability of encountering an entry falling below set thresholds in fluctuating nonbiological systems such as those encountered in quality control records.

1) Select the 100 example Count data values in column H (cells H2 to H101) and choose 'Clear> Contents' from the 'Edit' menu.

2) Manually enter new Count data directly into column H or paste data copied from another source into column H starting at cell H2 using the 'Paste Special...Values' command on the 'Edit' menu.

3) Enter up to 5 Survival Threshold values applicable to your own data into cells E2, E4, E6, E8 & E10.

4) Click the 'Update Worksheet' button to run the AutoUpdate macro.

5) Compare the Correlation Coefficient of the Count data (cell C8) with the Correlation Coefficient of the Ln(Count) data (cell D8).

6) For the chosen distribution, columns F (Normal) and G (Lognormal) contain probabilities (

Detailed 9-step instructions to manually update affected cells and charts in the worksheet when the Count data have changed.

a) Clear the example Count data values from column H (labeled 'Count (C)' and colored rose) by selecting them and choosing 'Clear> Contents' from the 'Edit' menu.

b) Enter your microbial Count data directly into column H or copy your data (1 column of numeric ASCII text values, 1 measurement per line, no gaps) from another source and choose 'Paste Special...Values' from the 'Edit' menu to paste them into the 'Count (C)' column starting at cell H2. The number (up to 16384) of microbial Count measurements that you pasted is shown in cell B2. If you pasted more than 16384 values you must change the 16385 in the formula in cell B2 to refer to a row number that is not less than the number of the row containing the last value that was pasted.

c) The Count data are plotted against their index numbers (i) on the line chart called 'Food Product #1 SPC'. Select the title and change it to refer to your own data.

d) Click on the chart and choose 'Source Data...' from the 'Chart' menu. Click on the 'Series' tab. Be sure the 'Values:' field specifies every row of Count data that you want to appear on the chart by setting the last row of the 'Values:' field (122 in the example chart) to a row that is 1 greater than the last row containing a Count data value (because row 2, not row 1, contains the first data point and we want to be sure the last point appears on the chart).

a) Non-empty white colored cells apply to autocorrelation calculations.

b) If the number of Lags (shown in cell B9) is less than the allowed maximum (25), click on the Autocorrelation Function chart and choose 'Source Data...' from the 'Chart' menu. Click on the 'Series' tab and set the last referenced row (initially 26) to be 1 greater than the value shown in cell B9.

c) Columns M through column (Q+# of Lags) must contain the same number of rows as column H. Clear the excess rows from those columns if they contain more rows than column H. If they contain fewer rows, copy and paste the last row from each column into the number of new rows needed to make each column the same length as column H.

Qualitatively:

1) If the Count Histogram has a symmetric bell shape, your data probably have a normal distribution. If the Ln(Count) Histogram has a symmetric bell shape, your data probably have a lognormal distribution.

2) Compare the Quantile-Quantile Plot of the Count data (normal distribution) with the Q-Q plot of the Ln(Count) data (lognormal distribution). Choose the distribution whose Q-Q Plot looks most like a straight line.

Compare the correlation coefficient of the Count data (cell C8) with the correlation coefficient of the Ln(Count) data (cell D8).

(See http://www.itl.nist.gov/div898/handbook/main.html for more about skewness. There are other formal statistical tests for normality or lognormality that are not included in this worksheet.)

1) Peleg, M. and Horowitz, J. 2000. On estimating the probability of aperiodic outbursts of microbial populations from their fluctuating counts. Bulletin Mathematical. Biology 62:17-35.

2) Peleg, M., Nussinovitch, A. and Horowitz, J. 2000. Interpretation and extraction of useful information from irregular fluctuating industrial microbial counts. J. Food Science 65:740-747.

3) Horowitz, J., Normand, M.D. and Peleg, M. 1999. On modeling the irregular fluctuations in microbial counts. Critical Reviews Food Science Nutrition 39(6):503-517.

4) Corradini, M.G., Normand, M.D., Nussinovitch A., Horowitz, J. and Peleg, M. 2001. Estimating the frequency of high microbial counts in industrial food products using various distribution functions. J. Food Prot. 65(3):674-681.

5) Peleg, M. 2006. Advanced quantitative microbiology for food and biosystems: Models for predicting growth and inactivation. CRC Press, Boca Raton FL.

6) Rosenkrantz, W.A. 2000. Confidence bands for quantile functions: A parametric and graphic alternative for testing goodness of fit. The American Statistician 54(3):185-190.

Estimating the Probability of High Microbial Counts Using Excel: http://people.umass.edu/aew2000/MicCountProb/microbecounts.html

Failure Probabilities from Quality Control Charts: http://demonstrations.wolfram.com/FailureProbabilitiesFromQualityControlCharts/

Simulating Microbial Count Records with an Expanded Fermi Solution Model: http://demonstrations.wolfram.com/SimulatingMicrobialCountRecordsWithAnExpandedFermiSolutionMo/

A Probabilistic Model for Population Extinction: http://demonstrations.wolfram.com/AProbabilisticModelForPopulationExtinction/

Model by:Micha Peleg Tel: 413-545-5852 Professor Fax: 413-545-1262 E-mail: micha.peleg@foodsci.umass.edu

Worksheet by:Mark D. Normand Tel: 413-545-2365 EDP Programmer II Fax: 413-545-1262 E-mail: mnormand@foodsci.umass.edu

Both located at:Department of Food Science Chenoweth Laboratory University of Massachusetts Amherst, MA 01003

http://people.umass.edu/~aew2000/EstimatingProbabilityOfAccidentalExtinction.html

The research on which this worksheet is based was initially supported by the USDA NRI (Food Safety) Program and is currently supported by the Massachusetts Agricultural Experiment Station at Amherst.