Estimating the number of victims in food poisoning outbreaks using Excel*

An Excel worksheet is used to estimate the number of cases of food poisoning based on the 'Fermi solution'. Once each influential factor is identified and its range estimated, a series of 'Fermi solutions' is generated using random values within the specified ranges. Theoretically, the distribution of the solutions should approach lognormality as the number of factors and repetitions grow. Nevertheless, approximately lognormal distribution is already evident with as few as 5-6 factors. This allows for computing a "best guess" from this distribution's mode. The method may be used to estimate the number of victims in spontaneous food poisoning episodes or in outbreaks caused by deliberate poisoning as in an act of bioterrorism.

The pop-up comment notes from all 3 Excel workbook files are also available on this page so that you may read and/or print them before using the files.

Version 3 (September 24, 2008) fixes a problem that would cause a "#DIV/0!" error in several places if for one or more factors the entered MinFactor and MaxFactor values were equal to each other. It also computes NHistBins and HistBinWidth in a slightly different manner. Version 2 (January 6, 2006) and later versions automatically count the number of user-entered min and max factor values (from 2 to 30). The user must also enter the number of desired iterations (repetitions). The solutions are generated by assigning random values to each factor within its specified range. (The random numbers themselves have a uniform distribution.) The program plots the solutions' histogram on which the corresponding lognormal distribution curve is superimposed. This lognormal distribution has the same mode and standard deviation as the generated solutions and its mode is considered the "best guess". The program also computes the analytical solution derived from the particular chosen factors' ranges.

The 'Run' button executes a Visual Basic macro to automatically update any cells and charts in the worksheet that are affected by changes in the number of factors and their ranges and the number of desired repetitions. You must click on the 'Enable Macros' button in the dialog box that is displayed before the worksheet opens in Excel in order for the macro to be useable.

* Excel® is a registered trademark of Microsoft Corporation. You can get more information about Excel by visiting Microsoft's web site at: http://www.microsoft.com/

The Excel worksheet (AutoFermiRiskEst.xls) is available in the file format of Excel 97 - 2003 for Windows and Excel 98, Excel 2001 and Excel X for MacOS. A second file (AutoFermiRiskEstMacXL2004.xls) is available only for Excel 2004 for MacOS. A third file (AutoFermiRiskEstXL2007.xlsm) is available only for Excel 2007 for Windows. If you have a modern web browser you should be able to download any of the Excel files directly by clicking on one of the links below. If Windows Excel 97-2003 or MacOS Excel 98, 2001, X or 2004 is currently installed on your computer, double-click the icon for the .xls file that was downloaded to open the worksheet in Excel. Be sure to click the 'Enable Macros' button in the dialog box that appears. If you are using Excel 2003 for Windows, you may need to first reduce the security level in Tools>Macro>Security to Medium, then close and reopen the file to enable the macros.

In Excel 2007 for Windows, enable macros as follows: click on the Office Button in the upper left corner of the screen then choose "Excel Options". Click on the "Trust Center" button in the list on the left then click the "Trust Center Settings..." button. In the "Model Settings" group click the "Enable all macros (not recommended; potentially dangerous code can run)" button then click "OK" to close the Trust Center Settings box and click "OK" again to close the "Excel Options" box.

The file that is downloaded for Excel 2007 is a .zip archive of XML files called "AutoFermiRiskEstXL2007.zip". Here are three ways to open it (in increasing order of difficulty): 1) Click on the filename and change the ".zip" extension to ".xlsm". Windows will ask if you really want to do this and you should click "Yes". The icon of the file will change to one for an Excel .xlsm file and from then on you can open it in Excel by simply double-clicking the icon. 2) If you have an Excel 2007 shortcut icon on your desktop, drag and drop the .zip file onto it. 3) Start Excel 2007 from the Start menu or from a shortcut then from the Office Button choose "Open", set the "Files of type:" selector to "All Files (*.*)" and choose the AutoFermiRiskEstXL2007.zip archive that was downloaded. Click "Yes" in response to the question that ends with "Do you want to open the file now?" If you use method 2) or 3) to open the file you should immediately save it by choosing "Save As..." from the Office Button and choose either the "Excel 2007 Macro-Enabled" (.xlsm) or "Excel 2007 Binary" (.xlsb) format which will allow you to open the saved file in the future by simply double-clicking its icon. You can then delete the downloaded .zip archive.

You are the visitor to this page since November 23, 2005.

Free Microsoft Excel Workbook Software

Prof. Micha Peleg

Mark D. Normand

UMass Department of Food Science

University of Massachusetts at Amherst