Comment Notes from Excel® Files:
AutoFermiRiskEst.xls, AutoFermiRiskEstMacXL2004.xls and AutoFermiRiskEstXL2007.xlsm

AutoFermiRiskEstXL2007.xlsm [A1] (seen in Windows Excel 2007 file AutoFermiRiskEstXL2007.xlsm only) or
AutoFermiRiskEstMacXL2004.xls [A1] (seen in MacOS Excel 2004 file AutoFermiRiskEstMacXL2004.xls only) or
AutoFermiRiskEst.xls [A1] <--This means "AutoFermiRiskEst.xls" is in cell A1. Below is the comment note that pops up when you point to cell A1.
AutoFermiRiskEst.xls or AutoFermiRiskEstMacXL2004.xls or AutoFermiRiskEstXL2007.xlsm: This workbook applies the "Fermi Solution" method to a set of probability margins to estimate the number of individuals affected among a group of people who are assumed to have consumed a contaminated food. This is done by creating a histogram of the estimated frequencies of the numbers of affected individuals. On each Run a new set of data is generated and a new histogram is created and its chart is updated. The user's input to the procedure (in pink colored cells) is NRepetitions, the number of repetitions of random trials to generate (in cell A6) along with MinFactors and MaxFactors, the minimum and maximum number of people who might have consumed the food and a list of minimum and maximum probabilities that they actually have been affected (in cells A30 thru A59 and B30 thru B59, respectively). Output from the procedure is the chart and the BestGuess values in the yellow colored cells E2, E4 and E6.

Instructions [A2]
Instructions: Automatic calculation has been turned off in this worksheet in Tools>Preferences...>Calculation to avoid generating new data every time any worksheet cell changes. Press the F9 key (also Cmd= if using MacOS) to generate a new data set and manually force recalculation of the worksheet. This version runs only in Windows Excel 2007. (seen in Windows Excel 2007 file AutoFermiRiskEstXL2007.xlsm only)

The number of repetitions of risk estimation data points to be generated in column P must be entered in the pink colored cell A6. The number of factors to use in computing the risk estimation data are counted by a formula in cell A4. Currently, 5 factors are used but a different number of min and max factor values (from 2 to 30) may be entered in the pink cells of rows 30 to 59 in columns A and B.

The minimum and maximum allowed values of all the factors used in generating the data in the EstData column are entered into the pink MinFactors (A30 thru A59) and MaxFactors (B30 thru B59) cells, respectively. Risk estimation data values are generated in column P starting with cell P2 using formulas that allow each of the factors to vary at random between the MinFactors values in cells $A$30 thru $A$59 and the MaxFactors values in cells $B$30 thru $B$59. (The $ is used to indicate an absolute cell reference that will not change when the formula in a cell is copied and pasted.) A value of 1 may be entered in the row of any currently used factor in both the MinFactors and MaxFactors columns to remove that factor from the calculations.

Click the Clear button to clear old data from columns C, D and P thru T and to delete the chart. Click the Run button to first Clear any existing data and chart and then generate new data and draw a new chart. The RoundBestGuess and ModelBestGuess of the number of food poisoning cases in the modeled group are found in cells E2 and E4, respectively. They are based on the assumption that the number of cases is lognormally distributed. AnSolBestGuess is the best guess found by an analytic solution of the problem and is located in cell E6. The associated lognormal distribution curve is also displayed on the historgram chart.

References [B1]
References: 1) An Introductory Guide to Uncertainty Analysis in Environmental and Health Risk Assessment, 1994, Hammonds, J.S., Hoffman, F.O., Bartell, S.M., Oak Ridge National Laboratory, Oak Ridge, Tennessee, 37831-6285.

2) The Fermi Solution: Essays on Science, Von Baeyer, H.C., 1993, Penguin, London.

Authors [B2]
Authors:
Model by:
Micha Peleg         Tel: 413-545-5852
Professor           Fax: 413-545-1262
                    E-mail: see http://www-unix.oit.umass.edu/~aew2000/
Worksheet by:
Mark D. Normand     Tel: 413-545-2365
EDP Programmer II   Fax: 413-545-1262
                    E-mail: see http://www-unix.oit.umass.edu/~adva000/
Both located at:
Department of Food Science
Chenoweth Laboratory
University of Massachusetts
Amherst, MA  01003

Worksheet on the Web at:
http://www-unix.oit.umass.edu/~aew2000/FermiRiskEst.html

Analytic Solution by:
Joseph Horowitz
Professor Emeritus
Department of Mathematics & Statistics
University of Massachusetts
Amherst, MA  01003

Last modified: September 24, 2008

NFactors [A3]
NFactors: The cell below contains a formula that counts the number of factors used to generate risk estimation data. Currently, 5 factor values are entered in cells A30 thru A34 and B30 thru B34. If more factor entries are needed, their min and max values should be entered into columns A and B, respectively, starting at row 35 (from 2 to 30 factors are currently allowed). The NFactors count and the formulas in columns C, D and P thru T will be updated on the next Run to include references to the added factors in columns A and B. Each data value in column P is currently calculated as the product of random values for the 5 factors as M * P1 * P2 * P3 * P4.

NRepetitions [A5]
NRepetitions: The cell below contains the number (a positive integer from 2 to 65535) of risk estimation data values to be generated in column P. The number of histogram bins is limited to 20, which occurs when NRepetitions is greater than or equal to 725.

Min(EstData) [B3]
Min(EstData): The cell below fixes the minimum risk estimation data value from column P at zero which sets the lower x-axis limit of the chart containing the histogram and lognormal distrubution curve.

Max(EstData) [B5]
Max(EstData): The cell below contains the maximum risk estimation data value from column P which sets the upper x-axis limit of the chart containing the histogram and lognormal distrubution curve.

NHistBins [C3]
NHistBins: The cell below calculates the number of bins to use in the histogram. NHistBins is limited to 20, which is reached when NRepetitions is greater than or equal to 725.

HistBinWidth [C5]
HistBinWidth: The cell below contains the width of each bin in the histogram of the risk estimation data in column P.

MeanLnEst [D3]
MeanLnEst: The cell below contains the mean of all the natural log risk estimation data values in column R.

StDevLnEst [D5]
StDevLnEst: The cell below contains the standard deviation of all the natural log risk estimation data values in column R.

RoundBestGuess [E1]
RoundBestGuess: The cell below contains the value in cell E4 rounded to the nearest integer. It is the "best guess" of the number of food poisoning cases in the modeled group.

ModelBestGuess [E3]
ModelBestGuess: The cell below contains the value of the x-coordinate at the maximum of the lognormal distribution curve plotted using the values in columns S and T. It is the "best guess" of the number of food poisoning cases computed by the simulation model.

AnSolBestGuess [E5]
AnSolBestGuess: The cell below contains the analytic solution for the best guess assuming a uniform probabilty distribution of the NFactors factors within their assigned ranges. ModelBestGuess, the "best guess" computed by the simulation model, should be approximately equal to AnSolBestGuess.

BinNumber [A7]
BinNumber: The 20 cells below contain the index number of each histogram bin for the risk estimation data in column P.

BinMaximum [B7]
BinMaximum: The 20 cells below contain the maximum value in each histogram bin of the risk estimation data in column P.

Frequency [C7]
Frequency: The 20 cells below contain the frequency histogram data for the risk estimation data in column P.

IntBinMax [D7]
IntBinMax: The 20 cells below contain the maximum value stored in each histogram bin rounded to the nearest integer. These are the x-axis bin labels of the histogram bar chart.

Frequency (%) [E7]
Frequency (%): The 20 cells below contain the frequency histogram data expressed as a percentage for the risk estimation data in column P. These are the y-axis values of the histogram bar chart.

MinFactors [A29]
MinFactors: The NFactors cells below contain the minimum allowed values for all probability factors used to generate the EstData risk estimation data in column P. All MinFactor values must be greater than zero and less than or equal to the corresponding MaxFactor value in column B. For details on each factor see the comment note attached to its MaxFactors cell.

MaxFactors [B29]
MaxFactors: The NFactors cells below contain the maximum allowed values for all probability factors used to generate the EstData risk estimation data in column P. All MaxFactor values must be greater than or equal to the corresponding MinFactor value in column A. For details on each factor see the comment note attached to its MaxFactors cell.

600 [B30]
M: The cell to the left and this one are the Mmin and Mmax allowed values, respectively, for M, the number of diners in the group under consideration. Mmin and Mmax must both be greater than zero and Mmin must be less than or equal to Mmax.

0.7 [B31]
P1: The cell to the left and this one are the P1min and P1max allowed values, respectively, for the probability factor P1, e.g., the fraction (0 < P1 <= 1) of diners served the contaminated food. P1min must be greater than zero and less than or equal to P1max.

0.6 [B32]
P2: The cell to the left and this one are the P2min and P2max allowed values, respectively, for the probability factor P2, e.g., the fraction (0 < P2 <= 1) of diners eating a sufficient quantity of the contaminated food. P2min must be greater than zero and less than or equal to P2max.

0.5 [B33]
P3: The cell to the left and this one are the P3min and P3max allowed values, respectively, for the probability factor P3, e.g., the fraction (0 < P3 <= 1) of diners in whom the pathogen managed to survive. P3min must be greater than zero and less than or equal to P3max.

0.8 [B34]
P4: The cell to the left and this one are the P4min and P4max allowed values, respectively, for the probability factor P4, e.g., the fraction (0 < P4 <= 1) of diners sensitive to the contaminating microorganism to the extent that they will develop clinical symptoms. P4min must be greater than zero and less than or equal to P4max.

AnSolMuTerms [C29]
AnSolMuTerms: The cells below compute the individual contributions of each factor to the mean of the analytic solution.

AnSolVarTerms [D29]
AnSolVarTerms: The cells below compute the individual contributions of each factor to the variance of the analytic solution.

HistArea (%) [E29]
HistArea (%): The cell below finds the area of the entire histogram (in %) by adding up the height of all the Frequency (%) bins.

ElogM [E31]
ElogM: The cell below contains the sum of the natural log of all the values from Mmin to Mmax. It is calculated and saved in that cell by the Run macro.

VlogM [E33]
VlogM: The cell below contains the sum of the square of the natural log of all the values from Mmin to Mmax. It is calculated and saved in that cell by the Run macro.

AnSolMean [E35]
AnSolMean: The cell below contains the average over all possible values of the NFactors factors. It is computed as the sum of all the AnSolMuTerms and is an intermediate value in the analytic solution.

AnSolVariance [E37]
AnSolVariance: The cell below contains the variance over all possible values of the NFactors factors. It is computed as the sum of all the AnSolVarTerms and is an intermediate value in the analytic solution.

*Excel 2004 Alert* [E39] (seen in MacOS Excel 2004 file AutoFermiRiskEstMacXL2004.xls only)
*Excel 2004 Alert*: If you are using Excel 2004 with MacOS X, you need to add a user-defined custom chart type to Excel in order for this file's macros to work. This only needs to be done one time, as follows: Click on the white background next to the chart title to select the chart. Choose "Chart Type..." from the Chart menu. Click the "Custom Types" button and click the "User-defined" button under "Select from". If you don't see the name "Line - Column on 2 Axes" on the "Chart type:" list, click the "Add..." button and type that name into both the "Name:" and "Description:" fields then click the "OK" button. Click "OK" to exit the Chart Type box. Quit then restart Excel and open this file. Now the "Run" button will work.

EstData [P1]
EstData: The NRepetitions cells below contain the current Fermi risk estimation data generated to vary at random using the min and max entered values of the NFactors factors.

IntEst [Q1]
IntEst: The cells below contain the estimation values from column P rounded to the nearest integer.

LnEst [R1]
LnEst: The cells below contain the natural log of the risk estimation values in column P.

LogNormX [S1]
LogNormX: The cells below contain the x-coordinate values for the lognormal distribution line plot.

LogNormY [T1]
LogNormY: The cells below contain the y-coordinate values for the lognormal distribution line plot.

[Return to ] Estimating the number of victims in food poisoning outbreaks Using Excel

Content last updated: September 24, 2008