Modeling Microbial Population Explosions Using Excel*

MicPopExplModel.xls
Generates Time and Population Count values from a parameterized mathematical model with user-adjustable parameters.
Current version: February 21, 2008


MicPopExplModel.xls: This Excel workbook generates from a parameterized mathematical model multiple sets of log-normally distributed random numbers representing microbial population counts. It draws charts of a user-selected Population Count data set versus Time in two dimensions and of all data sets in three dimensions. It identifies all peaks in the data whose height is greater than a user-entered threshold and charts the peak heights and widths versus a set of user-entered height and width limits and versus each other.

To generate log-normally distributed random population count data with this program the VisualBasic macro code uses the normally distribued random number generator (called RandNorm below) in Excel's Analysis ToolPak - VBA to implement the following model:

PopulationCount(i)=10^(MuLog10 +SigmaLog10*RandNorm)

where MuLog10 and SigmaLog10 are the base 10 logarithmic mean and standard deviation of the generated random fluctuations.

* 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/


Download the Demonstration Excel Workbook File

The demonstration workbook is available in a file format for Windows Excel 97, 2000, XP, 2003 and 2007. It does NOT run in any MacOS version of Excel due to MacOS Excel's inability to generate normally distributed random numbers from Visual Basic using the Analysis Toolpak - VBA. Microsoft is aware of the problem but has no plans to fix it. If you have a modern web browser you should be able to download the binary Excel file directly by clicking on the link below.

Generates Time and Population Count values using a mathematical model with user-adjustable parameters.
Excel binary file (262K) for Windows Excel 97, 2000, XP, 2003 and 2007:
Click here to download ==> MicPopExplModel.xls

Note: The first version of the Excel file was dated May 1, 2007. The February 21, 2008 version adds compatibility with Windows Excel 2007, adds one additional generated data point at Time=0 for each data set, improves tick mark labeling on the X (Time) and Z (DataSets) axes, changes the order of the 3D chart entries in column A from X,Z,Y to X,Y,Z and updates several comment notes.


Opening the Excel Workbook File

If you have Microsoft Excel installed on your hard drive, you should be able to open the MicPopExplModel.xls file in Excel by double-clicking on its icon. If that doesn't work, open the workbook file by first starting Excel, choosing Open... from the File menu and then selecting the Excel file that was downloaded and saved on your hard disk. If the file does not appear on the list of file names, set the Files of type: pop-up menu to All Files (*.*) then select and open the file. If you were unable to open the file by double-clicking on it, use the alternate method just described to open it and once the file has opened in Excel you should immediately save it by choosing Save from the File menu. This will make it possible to open the file in the future by simply double-clicking on its icon.

When you are shown a dialog box asking about enabling macros, click the Enable Macros button. If you are using a recent version of Excel for Windows, you may need to reduce the security level in Tools>Macro>Security to Medium, then close and reopen the file to enable the macros.

How to Run the Excel Worksheet Model

Clicking the Clear All button will erase the five charts from Sheet1 and any previously generated data cells on Sheet1 through Sheet16. Clicking the Generate Data button erases any data and charts before new data are generated so it is not necessary to click Clear All before clicking Generate Data. (For Excel 97-2003: If after clicking the Generate Data button you see an error box that says, "Runtime error '1004'; 'ATPVBAEN.XLA' could not be found.", go to the 'Tools' menu, choose 'Add-Ins...', check the 'Analysis ToolPak-VBA' checkbox and click 'OK'. The Generate Data button should then work without error. For Excel 2007: If after clicking the Generate Data button you see an error box that says,"Runtime error '1004'; 'ATPVBAEN.XLAM' could not be found.", click on the Office Button, choose 'Excel Options', click on the 'Add-Ins' category then click on 'Analysis ToolPak-VBA' in the list of Add-Ins and click the 'Go...' button. Be sure the 'Analysis ToolPak-VBA' box is checked in the list of Add-Ins available then click 'OK'. The Generate Data button should then work without error.) After data have been generated, click the Draw Charts button to draw five charts on Sheet1. You many make changes to any chart settings in columns A & B of Sheet1 and click the Draw Charts button again to redraw the charts using the new settings. Read the comment notes attached to cells A1 thru A5 for instructions on how to run the worksheet model. To view or print all of the workbook's comment notes together click on the link below.

Worksheet Comment Notes

The Excel file MicPopExplModel.xls contains 16 worksheets. Sheet1 contains instructions, user input cells, five embedded charts, and three buttons labeled Clear All, Generate Data and Draw Charts. Cells that have a red triangle marker in their upper right corner have a comment note attached to them.

Click here to see the text of all comment notes from MicPopExplModel.xls.

If a cell contains the name of one or more cells below it, the note explains how those cells are used. In Windows Excel 97 or newer the note pops up automatically when you use the mouse to point to a marked cell. To edit or change the size of a note, click on a marked cell to select it, then choose Edit Comment from the Insert menu. Making modifications to any of the formulas in a worksheet or to the Visual Basic macro code may cause the macros to stop working properly.


References

  1. Peleg, M. 2006. Advanced quantitative microbiology for food and biosystems: Models for predicting growth and inactivation. CRC Press, Boca Raton FL. (Click to download a 452K PDF file containing contents description.)

  2. Engel, R., Normand, M.D., Horowitz, J. and Peleg, M. 2001. A qualitative probabilistic model of microbial outbursts in foods. J. Sci. Food Agric. 81:1250-1262.

  3. Engel, R., Normand, M.D., Horowitz, J. and Peleg, M. 2001. A model of microbial contamination of a water reservoir. Bull. Math. Biol. 63:1025-1040.

You are the ********* visitor to this page since May 1, 2007.

[Return to ] Free Microsoft Excel Workbook Software

[Return to ]Prof. Micha Peleg

[Return to ] Mark D. Normand

[Return to ] UMass Department of Food Science

[Return to ] University of Massachusetts at Amherst

Content last updated: February 21, 2008