Comment Notes from Excel File: MicPopExplModel.xls

Current version: February 21, 2008

On Sheet1:

In Sheet1 column A:
MicPopExplModel.xls [A1] <--This means "MicPopExplModel.xls" is in cell A1. The comment note below pops up when you point to cell A1.
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.

The first version of this 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.

Background [A2]
Background: 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.

Values for the following Data Generation Model Settings are entered between the pink and tan cells of Sheet1 column A: NPoints, NDataSets, Use RandSeed, RandSeed, Dt, MuLog10, SigmaLog10, DetectThresh, ExplThresh, SignThresh, ExplGrowthFactor and ExplDeathFactor. ExplPeakThresh is the threshold level used to define a peak in the generated population data.

Values for the Chart Settings (e.g., index# of the DataSet to use on three of the 2D charts, axis limits, axis and chart titles, point marker and line attributes) are entered below the tan and yellow cells in column A and in column B of Sheet1.

The tick mark labels that Excel 2007 puts on the Category (X or Time) and Series (Z or DataSet) axes of the 3D chart are different than those drawn by Excel 97-2003. Excel 2007 correctly labels the Time axis from "0" to "100" in increments of 10 whereas the earlier version's labels start at "1" and end at numbers and with increments that vary depending upon the number of Series plotted. Excel 2007 labels the first of 20 Series as "HistBars" and the last as "Series20" whereas earlier versions label them as "S1" and "S20", respectively.

How to run [A3]
How to run: How to run: This workbook needs to run some macros. Therefore, you must click the Enable Macros button when opening it. 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 macros. This program does NOT run in any version of Excel for MacOS 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.
Enter an initial value for each model variable in the cells between the pink and tan cells in column A. The pop-up comment note that appears when the name cell above a variable's cell is pointed to with the mouse describes the variable and lists any restrictions on its legal range of values. You may click the Clear All button in column A to have the ClearAll macro delete any existing charts on Sheet1 and clear all chart data cells on Sheets 1 through 16.

Clicking the Generate Data button in column A first calls ClearData and ClearCharts then calls the GenData macro. The GenData macro will generate new data using the model settings entered between the pink and tan cells in coloumn A and save it on sheets 1 through 16. It also finds and counts all peaks in the data. Next, use the values written by the GenData macro into the green Max-titled cells of sheets 1-16 to help in entering AxisMax settings in columns 1 and 2 of Sheet1 for all the charts. Make any desired changes to the chart settings in column A below the tan cells (for the Population Count vs DataSet vs Time 3D chart) and below the yellow cells (for the Peak Height vs Peak Width chart) and in column B by entering a DataSet index number and other settings for the remaining three 2D charts (Population Count vs Time, No. of Height Peaks vs Peak Height Limit and No. of Width Peaks vs Peak Width Limit).

Clicking the Draw Charts button first calls ClearCharts then draws the five charts on Sheet1 using the previously generated data and entered chart settings. Make changes to any chart settings in columns A & B of Sheet1 and click the Draw Charts button again to redraw the charts.

Modifications [A4]
Modifications: Modifications: If you wish to see or modify the macros, they are accessed from the Tools>Macro>Visual Basic Editor menu item. Checking the legality of user entries is done by the ChartSetsOK and ModelSetsOK functions which call the NumOK and TextOK functions. All entry cells in columns A & B are assigned names by those functions. The names are then used in the data generation formulas on sheets 2 through 16 to refer to a cell in place of normal Excel cell references (such as B4 or R4C2). Unless absolutely necessary, changing the Visual Basic code should be avoided because an error can produce incorrect values or cause the program to stop working. This workbook runs only in Excel 97, 2000, XP, 2003 and 2007 for Windows. 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. Microsoft is aware of the problem but has no plans to fix it.

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.

References [A5]
References:
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.)

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.

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.

Model by:Micha Peleg, Robert Engel and Joseph Horowitz.
Program by: Mark D. Normand
Peleg & Normand at: UMass Department of Food Science, Chenoweth Lab., Amherst, MA 01003
Contact at:
Tel. (Peleg): 413-545-5852
Tel. (Normand): 413-545-2365
Fax: 413-545-1262
E-mail & web (Peleg), see: http://www-unix.oit.umass.edu/~aew2000/
E-mail & web (Normand), see: http://www-unix.oit.umass.edu/~adva000/

Last modified: February 21, 2008

Data Generation
Model Settings: [A14]
Data Generation Model Settings: The cells below between the pink and tan cells contain settings to control the model that generates random population data. Point to the name above each cell to see a pop-up comment note describing the use of that setting and any restrictions on the values that may be entered.

NPoints [A15]
NPoints: The cell below contains the number of random data values to generate per data set. It is the number of Dt time increments used in the model and shown on charts having a Time axis. NPoints must be an integer from 2 to 65535.

NDataSets [A17]
NDataSets: The cell below contains the number of random population count data sets to generate per run. This workbook contains 15 worksheets (Sheet2 through Sheet16) for generated data and each sheet may contain at most 42 data sets. NDataSets must be an integer from 2 to 630.

Use RandSeed [A19]
Use RandSeed: The cell below contains a flag that specifies whether or not to use the specified RandSeed value to generate repeatable random numbers. A different repeatable pseudo-random sequence is generated from each different positive integer RandSeed value if Use RandSeed is TRUE. Use RandSeed must be set to either TRUE or FALSE.

RandSeed [A21]
RandSeed: The cell below contains a positive integer value that is used to make the random number generator produce a repeatable sequence of pseudo-random values when Use RandSeed is set to TRUE. Each different positive integer RandSeed value generates a different repeatable pseudo-random sequence. RandSeed must be a positive integer from 1 to 32767.

Dt [A23]
Dt: The cell below contains the Time increment (in relevant units) between successive generated population count data values. Dt must be greater than 0.

MuLog10 [A25]
MuLog10: The cell below contains the base 10 logarithmic mean of the randomly generated fluctuations. MuLog10 must be greater than 0.

SigmaLog10 [A27]
SigmaLog10: The cell below contains the base 10 logarithmic standard deviation of the randomly generated fluctuations. SigmaLog10 must be greater than 0.

DetectThresh [A29]
DetectThresh: The cell below contains the smallest number of organisms that can be counted. DetectThresh must be greater than 0.

ExplThresh [A31]
ExplThresh: The cell below contains the threshold for the onset of a population explosion in the randomly generated data. ExplThresh must be greater than or equal to DetectThresh.

SignThresh [A33]
SignThresh: The cell below contains the probability of reversing direction during a population explosion (i.e., that the count will stop rising and start to decline). SignThresh must be greater than or equal to 0 and less than 1.

ExplGrowthFactor [A35]
ExplGrowthFactor: The cell below contains the population explosion growth factor. It determines the steepness of the population increase during a population explosion. ExplGrowthFactor must be greater than or equal to 1.

ExplDeathFactor [A37]
ExplDeathFactor: The cell below contains the population explosion death factor. It determines the steepness of the population decrease after the peak in a population explosion. ExplDeathFactor must be greater than or equal to 1.

ExplPeakThresh [A39]
ExplPeakThresh: The cell below contains the threshold value used to define a peak in the randomly generated data. ExplPeakThresh must be greater than ExplThresh.

3D Population Count
Chart Settings [A42]
3D Population Count Chart Settings: The following cells allow the user to set the values of certain attributes of the DataSet vs Population Count vs Time 3D chart on this worksheet. In some cases restrictions are placed on the values that may be entered.

StartTimePt3D [A43]
StartTimePt3D: The cell below contains the index of the first point to plot on the Time or x-axis of the DataSet vs Population Count vs Time 3D chart. StartTimePt3D must be an integer from 1 to NPoints-1.

EndTimePt3D [A45]
EndTimePt3D: The cell below contains the index number of the last point to plot on the Time or x-axis of the DataSet vs Population Count vs Time 3D chart. EndTimePt3D must be integer from 2 to NPoints.

Time3DAxisTitle [A47]
Time3DAxisTitle: The cell below contains the title for the Time or x-axis on the DataSet vs Population Count vs Time 3D chart. Time3DAxisTitle can contain from 0 to 255 characters.

PopCount3DAxisMin [A49]
PopCount3DAxisMin: The cell below contains the minimum value for the Population Count or y-axis on the DataSet vs Population Count vs Time 3D chart. PopCount3DAxisMin must be greater than or equal to 0.

PopCount3DAxisMax [A51]
PopCount3DAxisMax: The cell below contains the maximum value for the Population Count or y-axis on the DataSet vs Population Count vs Time 3D chart. PopCount3DAxisMax must be greater than 0. and greater than PopCount3DAxisMin.

PopCount3DAxisTitle [A53]
PopCount3DAxisTitle: The cell below contains the title for the Population Count or y-axis on the DataSet vs Population Count vs Time 3D chart. PopCount3DAxisTitle can contain from 0 to 255 characters.

StartDataSet3D [A55]
StartDataSet3D: The cell below contains the index number of the first DataSet to plot on the z-axis of the DataSet vs Population Count vs Time 3D chart. StartDataSet3D must be an integer from 1 to NDataSets-1.

EndDataSet3D [A57]
EndDataSet3D: The cell below contains the index number of the last DataSet to plot on the DataSet or z-axis of the DataSet vs Population Count vs Time 3D chart. EndDataSet3D must be an integer from 2 to NDataSets.

DataSet3DAxisTitle [A59]
DataSet3DAxisTitle: The cell below contains the title for the DataSet or z-axis on the DataSet vs Population Count vs Time 3D chart. DataSet3DAxisTitle can contain from 0 to 255 characters.

PopCount3DMainTitle [A61]
PopCount3DMainTitle: The cell below contains the main title for the DataSet vs Population Count vs Time 3D chart. PopCount3DMainTitle can contain from 0 to 255 characters.

Peak Width vs Peak Height
Chart Settings [A64]
Peak Width vs Peak Height Chart Settings: The following cells allow the user to set the values of certain attributes of the Peak Width vs Peak Height chart on this worksheet. In some cases restrictions are placed on the values that may be entered.

PeakHeightAxisMin [A65]
PeakHeightAxisMin: The cell below contains the minimum value for the Peak Height axis on the Peak Width vs Peak Height chart. PeakHeightAxisMin must be greater than or equal to 0.

PeakHeightAxisMin [A67]
PeakHeightAxisMin: The cell below contains the maximum value for the Peak Height axis on the Peak Width vs Peak Height chart. PeakHeightAxisMax must be greater than 0 and greater than PeakHeightAxisMin.

PeakHeightAxisTitle [A69]
PeakHeightAxisTitle: The cell below contains the title for the Peak Height axis on the Peak Width vs Peak Height chart. PeakHeightAxisTitle can contain from 0 to 255 characters.

PeakWidthAxisMin [A71]
PeakWidthAxisMin: The cell below contains the minimum value for the Peak Width axis on the Peak Width vs Peak Height chart. PeakWidthAxisMin must be greater than or equal to 0.

PeakWidthAxisMax [A73]
PeakWidthAxisMax: The cell below contains the maximum value for the Peak Width axis on the Peak Width vs Peak Height chart. PeakWidthAxisMax must be greater than 0 and greater than PeakWidthAxisMin.

PeakWidthAxisTitle [A75]
PeakWidthAxisTitle: The cell below contains the title for the Peak Width axis on the Peak Width vs Peak Height chart. PeakWidthAxisTitle can contain from 0 to 255 characters.

PeakWidthMainTitle [A77]
PeakWidthMainTitle: The cell below contains the main title for the Peak Width vs Peak Height chart. PeakWidthMainTitle can contain from 0 to 255 characters.

PeakWidthMarkerStyle [A79]
PeakWidthMarkerStyle: The cell below contains an index for the MarkerStyle of the Peak Width vs Peak Height chart. PeakWidthMarkerStyle may be either 1=Square, 2=Diamond, 3=Triangle, 4=Dash, 5=Star, 6=Dot, 7=X, 8=Circle or 9=Plus.

PeakWidthMarkerSize [A81]
PeakWidthMarkerSize: The cell below contains the size (in points, where 1pt.=1/72 inch) of the data point markers on the Peak Width vs Peak Height chart. PeakWidthMarkerSize must be an integer from 2 to 72.

PeakWidthMarkerColor [A83]
TimeInit: The cell below contains an index for the MarkerColor of the Peak Width vs Peak Height chart. PeakWidthMarkerColor may be either 1=Black, 2=White (Not allowed), 3=Red, 4=Bright Green, 5=Blue, 6=Yellow, 7=Pink, 8=Turquoise, 9=Dark Red, 10=Green, 11=Dark Blue, 12=Dark Yellow, 13=Violet, 14=Teal, 15=25% Gray or 16=50% Gray.

In Sheet1 column B:
2D Charts DataSet [B1]
2D Charts DataSet: The cell below contains the index number of the DataSet used to draw the 3 two-dimensional (2D) charts: Population Count vs Time, No. of Height Peaks vs Peak Height Limit and No. of Width Peaks vs Peak Width Limit. It must be an integer from 1 to NDataSets.

2D Population Count
Chart Settings [B4]
2D Population Count Chart Settings: The following cells allow the user to set the values of certain attributes of the Population Count vs Time 2D chart on this worksheet. In some cases restrictions are placed on the values that may be entered.

Time2DAxisMin [B5]
Time2DAxisMin: The cell below contains the minimum value for the Time axis on the Population Count vs Time 2D chart. Time2DAxisMin must be greater than or equal to 0.

Time2DAxisMax [B7]
Time2DAxisMax: The cell below contains the maximum value for the Time axis on the Population Count vs Time 2D chart. Time2DAxisMax must be greater than 0 and greater than Time2DAxisMin.

Time2DAxisTitle [B9]
Time2DAxisTitle: The cell below contains the title for the Time axis on the Population Count vs Time 2D chart. Time2DAxisTitle can contain from 0 to 255 characters.

PopCount2DAxisMin [B11]
PopCount2DAxisMin: The cell below contains the minimum value for the Population Count axis on the Population Count vs Time 2D chart. PopCount2DAxisMin must be greater than or equal to 0.

PopCount2DAxisMax [B13]
PopCount2DAxisMax: The cell below contains the maximum value for the Population Count axis on the Population Count vs Time 2D chart. PopCount2DAxisMax must be greater than 0 and greater than PopCount2DAxisMin.

PopCount2DAxisTitle [B15]
PopCount2DAxisTitle: The cell below contains the title for the Population Count axis on the Population Count vs Time 2D chart. PopCount2DAxisTitle can contain from 0 to 255 characters.

PopCount2DMainTitle [B17]
PopCount2DMainTitle: The cell below contains the main title for the Population Count vs Time 2D chart. PopCount2DMainTitle can contain from 0 to 255 characters.

PopCount2DLineStyle [B19]
PopCount2DLineStyle: The cell below contains an index for the LineStyle of the Population Count vs Time curve on the 2D chart. PopCount2DLineStyle may be either 1=Continuous, 2=Dash, 3=Dot, 4=DashDot or 5=DashDotDot.

PopCount2DLineWeight [B21]
PopCount2DLineWeight: The cell below contains an index for the LineWeight of the Population Count vs Time curve on the 2D chart. PopCount2DLineWeight may be either 1=Thin, 2=Medium or 3=Heavy.

PopCount2DLineColor [B23]
PopCount2DLineColor: The cell below contains an index for the LineColor of the Population Count vs Time curve on the 2D chart. PopCount2DLineColor may be either 1=Black, 2=White (Not allowed), 3=Red, 4=Bright Green, 5=Blue, 6=Yellow, 7=Pink, 8=Turquoise, 9=Dark Red, 10=Green, 11=Dark Blue, 12=Dark Yellow, 13=Violet, 14=Teal, 15=25% Gray or 16=50% Gray.

No. of Height Peaks
Chart Settings [B26]
No. of Height Peaks Chart Settings: The following cells allow the user to set the values of certain attributes of the No. of Height Peaks vs Peak Height Limit chart on this worksheet. In some cases restrictions are placed on the values that may be entered.

PeakHgtLimitAxisMin [B27]
PeakHgtLimitAxisMin: The cell below contains the minimum value for the Peak Height Limit axis on the No. of Height Peaks vs Peak Height Limit chart. PeakHgtLimitAxisMin must be greater than or equal to 0.

PeakHgtLimitAxisMax [B29]
PeakHgtLimitAxisMax: The cell below contains the maximum value for the Peak Height Limit axis on the No. of Height Peaks vs Peak Height Limit chart. PeakHgtLimitAxisMax must be greater than 0 and greater than PeakHgtLimitAxisMin.

PeakHgtLimitAxisTitle [B31]
PeakHgtLimitAxisTitle: The cell below contains the title for the Peak Height Limit axis on the No. of Height Peaks vs Peak Height Limit chart. PeakHgtLimitAxisTitle can contain from 0 to 255 characters.

NHgtPeaksAxisMin [B33]
NHgtPeaksAxisMin: The cell below contains the minimum value for the No. of Height Peaks axis on the No. of Height Peaks vs Peak Height Limit chart. NHgtPeaksAxisMin must be greater than or equal to 0.

NHgtPeaksAxisMax [B35]
NHgtPeaksAxisMax: The cell below contains the maximum value for the No. of Height Peaks axis on the No. of Height Peaks vs Peak Height Limit chart. NHgtPeaksAxisMax must be greater than 0 and greater than NHgtPeaksAxisMin.

NHgtPeaksAxisTitle [B37]
NHgtPeaksAxisTitle: The cell below contains the title for the No. of Height Peaks axis on the No. of Height Peaks vs Peak Height Limit chart. NHgtPeaksAxisTitle can contain from 0 to 255 characters.

NHgtPeaksMainTitle [B39]
NHgtPeaksMainTitle: The cell below contains the main title for the No. of Height Peaks vs Peak Height Limit chart. NHgtPeaksMainTitle can contain from 0 to 255 characters.

NHgtPeaksMarkerStyle [B41]
NHgtPeaksMarkerStyle: The cell below contains an index for the MarkerStyle of the No. of Height Peaks vs Peak Height Limit chart. NHgtPeaksMarkerStyle may be either 1=Square, 2=Diamond, 3=Triangle, 4=Dash, 5=Star, 6=Dot, 7=X, 8=Circle or 9=Plus.

NHgtPeaksMarkerSize [B43]
NHgtPeaksMarkerSize: The cell below contains the size (in points, where 1pt.=1/72 inch) of the data point markers on the No. of Peak Heights vs Peak Height Limit chart. NHgtPeaksMarkerSize must be an integer from 2 to 72.

NHgtPeaksMarkerColor [B45]
NHgtPeaksMarkerColor: The cell below contains an index for the MarkerColor of the No. of Height Peaks vs Peak Height Limit chart. NHgtPeaksMarkerColor may be either 1=Black, 2=White (Not allowed), 3=Red, 4=Bright Green, 5=Blue, 6=Yellow, 7=Pink, 8=Turquoise, 9=Dark Red, 10=Green, 11=Dark Blue, 12=Dark Yellow, 13=Violet, 14=Teal, 15=25% Gray or 16=50% Gray.

No. of Width Peaks
Chart Settings [B48]
No. of Width Peaks Chart Settings: The following cells allow the user to set the values of certain attributes of the No. of Width Peaks vs Peak Width Limit chart on this worksheet. In some cases restrictions are placed on the values that may be entered.

PeakWidLimitAxisMin [B49]
PeakWidLimitAxisMin: The cell below contains the minimum value for the Peak Width Limit axis on the No. of Width Peaks vs Peak Width Limit chart. PeakWidLimitAxisMin must be greater than or equal to 0.

PeakWidLimitAxisMax [B51]
PeakWidLimitAxisMax: The cell below contains the maximum value for the Peak Width Limit axis on the No. of Width Peaks vs Peak Width Limit chart. PeakWidLimitAxisMax must be greater than 0 and greater than PeakWidLimitAxisMin.

PeakWidLimitAxisTitle [B53]
PeakWidLimitAxisTitle: The cell below contains the title for the Peak Width Limit axis on the No. of Width Peaks vs Peak Width Limit chart. PeakWidLimitAxisTitle can contain from 0 to 255 characters.

NWidPeaksAxisMin [B55]
NWidPeaksAxisMin: The cell below contains the minimum value for the No. of Width Peaks axis on the No. of Width Peaks vs Peak Width Limit chart. NWidPeaksAxisMin must be greater than or equal to 0.

NWidPeaksAxisMax [B57]
NWidPeaksAxisMax: The cell below contains the maximum value for the No. of Width Peaks axis on the No. of Width Peaks vs Peak Width Limit chart. NWidPeaksAxisMax must be greater than 0 and greater than NWidPeaksAxisMin.

NWidPeaksAxisTitle [B59]
NWidPeaksAxisTitle: The cell below contains the title for the No. of Width Peaks axis on the No. of Width Peaks vs Peak Width Limit chart. NWidPeaksAxisTitle can contain from 0 to 255 characters.

NWidPeaksMainTitle [B61]
NWidPeaksMainTitle: The cell below contains the main title for the No. of Width Peaks vs Peak Width Limit chart. NWidPeaksMainTitle can contain from 0 to 255 characters.

NWidPeaksMarkerStyle [B63]
NWidPeaksMarkerStyle: The cell below contains an index for the MarkerStyle of the No. of Width Peaks vs Peak Width Limit chart. NWidPeaksMarkerStyle may be either 1=Square, 2=Diamond, 3=Triangle, 4=Dash, 5=Star, 6=Dot, 7=X, 8=Circle or 9=Plus.

NWidPeaksMarkerSize [B65]
NWidPeaksMarkerSize: The cell below contains the size (in points, where 1pt.=1/72 inch) of the data point markers on the No. of Peak Widths vs Peak Width Limit chart. NWidPeaksMarkerSize must be an integer from 2 to 72.

NWidPeaksMarkerColor [B67]
NWidPeaksMarkerColor: The cell below contains an index for the MarkerColor of the No. of Width Peaks vs Peak Width Limit chart. NWidPeaksMarkerColor may be either 1=Black, 2=White (Not allowed), 3=Red, 4=Bright Green, 5=Blue, 6=Yellow, 7=Pink, 8=Turquoise, 9=Dark Red, 10=Green, 11=Dark Blue, 12=Dark Yellow, 13=Violet, 14=Teal, 15=25% Gray or 16=50% Gray.

In Sheet1 column C:
MaxPopCount [C50]
MaxPopCount: The cell below contains the maximum Population Count value found over all the NDataSets DataSets. MaxPopCount is filled in by the GenData macro and is useful for setting the PopCount3DAxisMax value in Sheet1 cell A58 for the 3D Population Count vs DataSet vs Time chart.

MaxPeakHgt [C52]
MaxPeakHgt: The cell below contains the maximum Peak Height value found over all the NDataSets DataSets. MaxPeakHgt is filled in by the GenData macro and is useful for setting the PeakHeightAxisMax value in Sheet1 cell A68 for the Peak Width vs Peak Height chart.

PeakHgtLimit [C56]
PeakHgtLimit: TThe cells below contain the values of cutoff limits used to count peak heights in the user specified 2D Charts DataSet. The PeakHgtLimit values are the x-coordinates of the No. of Height Peaks vs Peak Height Limit chart data points.

In Sheet1 column D:
MaxPCDataSet [D50]
MaxPCDataSet: The cell below contains the index number of the DataSet that contains the maximum Population Count value over all DataSets. MaxPCDataSet is filled in by the GenData macro and is useful for deciding which DataSet to choose as the 2D Charts DataSet in Sheet1 cell B2.

MaxPHDataSet [D52]
MaxPHDataSet: The cell below contains the index number of the DataSet that contains the maximum Peak Height value over all DataSets. MaxPHDataSet is filled in by the GenData macro and is useful for deciding which DataSet to choose as the 2D Charts DataSet in Sheet1 cell B2.

MaxNHgtPeaks [D54]
MaxNHgtPeaks: The cell below contains the maximum value from the NHeightPeaks column below searching over all the DataSets. MaxNHgtPeaks is filled in by the GenData macro and is useful for setting the NHgtPeaksAxisMax value in Sheet1 cell B36 for the No. of Height Peaks vs Peak Height Limit chart.

NHeightPeaks [D56]
NHeightPeaks: The cells below contain a count of the number of peak heights in the 2D Charts DataSet that are greater than or equal to the corresponding value in the PeakHgtLimit column to the left. NHeightPeaks values are the y-coordinates in the No. of Height Peaks vs Peak Height Limit chart.

In Sheet1 column E:
MaxPeakWid [E52]
MaxPeakWid: The cell below contains the maximum Peak Width value found over all the NDataSets DataSets. MaxPeakWid is filled in by the GenData macro and is useful for setting the PeakWidthAxisMax value in Sheet1 cell A74 for the Peak Width vs Peak Height chart.

PeakWidLimit [E56]
PeakWidLimit: The cells below contain the values of cutoff limits used to count peak widths in the user specified 2D Charts DataSet. The PeakWidLimit values are the x-coordinates of the No. of Width Peaks vs Peak Width Limit chart.

In Sheet1 column F:
MaxPWDataSet [F52]
MaxPWDataSet: The cell below contains the index number of the DataSet that contains the maximum Peak Width value over all DataSets. MaxPWDataSet is filled in by the GenData macro and is useful for deciding which DataSet to choose as the 2D Charts DataSet in Sheet1 cell B2.

MaxNWidPeaks [F54]
MaxNWidPeaks: The cell below contains the maximum value from the NWidthPeaks column below searching over all the DataSets. MaxNWidPeaks is filled in by the GenData macro and is useful for setting the NWidPeaksAxisMax value in Sheet1 cell B58 for the No. of Width Peaks vs Peak Width Limit chart.

NWidthPeaks [F56]
NWidthPeaks: The cells below contain a count of the number of peak widths in the 2D Charts DataSet that are greater than or equal to the corresponding value in the PeakWidLimit column to the left. The NWidthPeaks values are the y-coordinates in the No. of Width Peaks vs Peak Width Limit chart.

On Sheet2:

In Sheet2 column A:
NormRand1 [A1]
NormRand1: The cells below contain the NPoints normally distributed random values for DataSet1 generated by the Analysis ToolPak-VBA and stored there by the GenData macro.

In Sheet2 column B:
Time1 [B1]
Time1: The cells below contain the NPoints Time values for DataSet1 generated and stored there by the GenData macro.

In worksheet column C:
PopCount1 [C1]
PopCount1: The cells below contain the NPoints Population Count values (i.e., the height if it's a population peak) for DataSet1 generated and stored there by the GenData macro.

In Sheet2 column D:
MaxTime1 [D2]
MaxTime1: The cell below contains the maximum Time value for DataSet1 found in column B. MaxTime1 is useful for setting the Time2DAxisMax value if DataSet1 is plotted on the 2D Population Count vs Time chart on Sheet1.

MaxPkTime1 [D4]
MaxPkTime1: The cell below contains the maximum PeakTime1 value for DataSet1 found in the column below.

PeakTime1 [D6]
PeakTime1: The cells below contain the Time values for the peaks found in the generated DataSet1 data in the columns to the left by the GenData macro.

In Sheet2 column E:
MaxPopHgt1 [E2]
MaxPopHgt1: The cell below contains the maximum PopCount1 value for DataSet1 found in column C. MaxPopHgt1 is useful for setting the PopCount2DAxisMax value if DataSet1 is plotted on the 2D Population Count vs Time chart on Sheet1.

MaxPkHgt1 [E4]
MaxPkHgt1: The cell below contains the maximum PeakHeight1 value for DataSet1 found in the column below.

PeakHeight1 [E6]
PeakHeight1: The cells below contain the Peak Height (Population Count) values for the peaks found in the generated DataSet1 data in the PopCount1 column to the left by the GenData macro.

In Sheet2 column F:
NPeaks1 [F2]
NPeaks1: The cell below contains the number of peaks for DataSet1 found in the Time1 and PopCount1 columns to the left and is the number of Time, Height and Width values saved in the columns below. The threshold defining what counts as a peak is the user-entered value of ExplPeakThresh in cell A40 on Sheet1.

MaxPkWid1 [F4]
MaxPkWid1: The cell below contains the maximum PeakWidth1 value for DataSet1 found in the column below.

PeakWidth1 [F6]
PeakWidth1: The cells below contain the Peak Width values for the peaks found in the generated DataSet1 data in the PopCount1 and Time1 columns to the left by the GenData macro.

Modeling Microbial Population Explosions Using Excel

Content last updated: February 21, 2008