## 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.

Content last updated: September 24, 2008