Comment notes from Excel file: RealTimeSterilizationData5.xls

Current version: February 4, 2008

In worksheet column A:
RealTimeSterilizationData5.xls [A1] <--This means "RealTimeSterilizationData5.xls" is in cell A1. The comment note below pops up when you point to cell A1.
RealTimeSterilizationData5.xls: This Excel workbook simulates the diminishing survival ratio of targeted microbial spores during a heat process intended for their elimination in real time. From 1 to 5 curves may be calculated from their entered Time & Temperature data. The results are plotted on four charts. This version of the program simulates the inactivation of spores whose isothermal survival curve follows the Weibull (power law) model with a fixed shape factor (power), n, and where the temperature dependence of the survival rate parameter, b(Temp), obeys a log logistic relationship. Explanation of the model can be found in Peleg, M. 2003. (see References).

The five data sets need not apply only to spores but can also describe chemical changes such as nutrient or pigment losses. Also, one can apply the same inactivation model to different temperature histories.

For more information on the concept, its various applications in calculating the efficacy of thermal and non-thermal microbial inactivation processes and biochemical reaction kinetics and its implementation with more complicated survival models, contact Micha Peleg at: http://www-unix.oit.umass.edu/~aew2000/

Background [A2]
Background: For generating the first survival curve from measured time and temperature DataSet_1, the user needs to enter Time values (in minutes) into worksheet column R starting at cell R2 and Temperature values (in degrees C) corresponding to each Time value into worksheet column S starting at cell S2. For DataSet_2 the Time values go in column W and the Temperature values in column X. For DataSet_3 the Time values go in column AB and the Temperature values in column AC. For DataSet_4 the Time values go in column AG and the Temperature values in column AH. For DataSet_5 the Time values go in column AL and the Temperature values in column AM. The other data columns in the range from R to AP will be filled in by the Solve macro.

The survival parameters are: n, k and TempCrit. The default values are for the spores of a C. botulinum-like organism: n= 0.35, k=0.309 C^-1 and TempCrit=102.45 C. The user may set them to other values as needed.

How to run [A3]
How to run: This workbook needs to run some macros. Therefore, you must click the Enable Macros button when opening the workbook.

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.

Make any desired changes to the overall chart settings in column A.

For each of the 5 curves that you wish to appear on the charts, enter a "y" or "Y" as the first character in row 2 of its IncludeDataSet cell in columns B through F. Enter an initial value for each model parameter (above the yellow line) and curve appearance setting (between the yellow and pink lines) in columns B through F.

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 the legal range of values for that variable.

Once legal values have been entered for each variable, you may click the Clear button in column A to have the Clear macro initialize the calculation cells (beow the pink line) in columns C through F, clear the graphical data columns R through AP and delete any existing charts.

Clicking the Solve button in column A first calls Clear then calls the Solve macro. The macro will solve the Log Survival equation and update the model and graphical data cells in columns C through F and R through AP. It will also draw four charts: the Temperature vs Time, the corresponding Log Survival vs Time (log base 10), the Equivalent Time at the Chosen Reference Temperature vs Time and the Weibullian Parameter b vs Time.

Modifications [A4]
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 Init function which calls the NumOK and TextOK functions. All numeric cells in columns B through F are assigned names by the Init function. Those names are then used in the formulas in columns B through F 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 program runs properly in Windows Excel 97, 2000, XP, 2003 and 2007 and in Excel 2001 for the Mac.

Speed: With all versions of Excel the speed of the computations is greatly improved (by 20 times or more) if Application.ScreenUpdating = False in the Solve macro, which is the way it is currently set. In that case, however, you cannot watch the charts being drawn. They will only appear after the final OK button is clicked.

Mac Problems: If you are using a pre-OS X version of MacOS running Excel 98 and receive a Run-time error saying "Method 'MaxChange' of object '_Application' failed" you need to comment out the line .MaxChange=0.000001 in the Clear subroutine by inserting a single-quote (') as the first character on the line. This is due to a bug in Excel 98. You should then enter 0.000001 manually in the Maximum change: edit box of Tools>Preferences...>Calculation.

Continuous ScreenUpdating is off by default in Excel X and 2004 for MacOS X and we don't know how to reenable it. Therefore, there is no option to watch the data and charts update in real time in Excel X and 2004. However, they will appear once the solution computations have finished and the final OK button is clicked. The change described above under Speed is always False with Mac Excel X and 2004.

References [A5]
References:
Peleg, M. and Penchina, C.M. 2000. Modeling microbial survival during exposure to a lethal agent with varying intensity. Crit. Rev. Food Sci. 40:159-172.

Peleg, M. 2003. Microbial survival curves: Interpretation, mathematical modeling and utilization. Comments on Theoretical Biology 8:357-387.

Peleg, M., Normand, M.D. and Corradini, M.G. 2005. Generating microbial survival curves during thermal processing in real time. Journal of Applied Microbiology 98:406-417.

Model by: Professor Micha Peleg
Program by: Mark D. Normand, EDP Programmer II
Both at:
UMass Department of Food Science
Chenoweth Lab.
Amherst, MA 01003
Tel. (Peleg): 413-545-5852
Tel. (Normand): 413-545-2365
Fax: 413-545-1262
Email & Web (Peleg), see: http://www-unix.oit.umass.edu/~aew2000/
Email & Web (Normand), see: http://www-unix.oit.umass.edu/~adva000/

Last modified: February 4, 2008


Chart Settings: [A13]
Chart Settings: The following cells allow the user to set the values of certain attributes of the charts on this worksheet. In some cases restrictions are placed on the values that may be entered.

TimeAxisMin [A14]
TimeAxisMin: The cell below contains the minimum value for the Time axis on all four charts. TimeAxisMin must be greater than or equal to 0.

TimeAxisMax [A16]
TimeAxisMax: The cell below contains the maximum value for the Time axis on on all four charts. TimeAxisMax must be greater than 0 and greater than TimeAxisMin.

TimeAxisTitle [A18]
TimeAxisTitle: The cell below contains the title for the Time axis on on all four charts. TimeAxisTitle can contain from 0 to 255 characters.

-------------------------------
TempAxisMin [A21]
TempAxisMin: The cell below contains the minimum value for the Temperature axis on the Temp vs Time chart. TempAxisMin must be greater than or equal to 0.

TempAxisMax [A23]
TempAxisMax: The cell below contains the maximum value for the Temperature axis on the Temp vs Time chart. TempAxisMax must be greater than 0 and greater than TempAxisMin.

TempAxisTitle [A25]
TempAxisTitle: The cell below contains the title for the Temperature axis on the Temp vs Time chart. TempAxisTitle can contain from 0 to 255 characters.

TempMainTitle [A27]
TempMainTitle: The cell below contains the main title for the Temp vs Time chart. TempMainTitle can contain from 0 to 255 characters.

TempLegend [A29]
TempLegend: The cell below specifies whether or not the Legend is to be displayed on the Temp vs Time chart. Enter "y" if it is or "n" if not.

-------------------------------
LSAxisMin [A32]
LSAxisMin: The cell below contains the minimum value for the Log Survival axis on the LS vs Time chart. LSAxisMin must be less than 0 and less than LSAxisMax.

LSAxisMax [A34]
LSAxisMax: The cell below contains the maximum value for the Log Survival axis on the LS vs Time chart. LSAxisMax must be less than or equal to 0.

LSAxisTitle [A36]
LSAxisTitle: The cell below contains the title for the Log Survival axis on the LS vs Time chart. LSAxisTitle can contain from 0 to 255 characters.

LSMainTitle [A38]
LSMainTitle: The cell below contains the main title for the LS vs Time chart. LSMainTitle can contain from 0 to 255 characters.

LSLegend [A40]
LSLegend: The cell below specifies whether or not the Legend is to be displayed on the LS vs Time chart. Enter "y" if it is or "n" if not.

-------------------------------
EqTimeAxisMin [A43]
EqTimeAxisMin: The cell below contains the minimum value for the EqTime axis on the EqTime vs Time chart. EqTimeAxisMin must be greater than or equal to 0.

EqTimeAxisMax [A45]
EqTimeAxisMax: The cell below contains the maximum value for the EqTime axis on the EqTime vs Time chart. EqTimeAxisMax must be greater than 0 and greater than EqTimeAxisMin.

EqTimeAxisTitle [A47]
EqTimeAxisTitle: The cell below contains the title for the EqTime axis on the EqTime vs Time chart. EqTimeAxisTitle can contain from 0 to 255 characters.

EqTimeMainTitle [A49]
EqTimeMainTitle: The cell below contains the main title for the EqTime vs Time chart. EqTimeMainTitle can contain from 0 to 255 characters.

EqTimeLegend [A51]
EqTimeLegend: The cell below specifies whether or not the Legend is to be displayed on the EqTime vs Time chart. Enter "y" if it is or "n" if not.

-------------------------------
bAxisMin [A54]
bAxisMin: The cell below contains the minimum value for the b axis on the b vs Time chart. bAxisMin must be greater than or equal to 0.

bAxisMax [A56]
bAxisMax: The cell below contains the maximum value for the b axis on the b vs Time chart. bAxisMax must be greater than 0 and greater than bAxisMin.

bAxisTitle [A58]
bAxisTitle: The cell below contains the title for the b axis on the b vs Time chart. bAxisTitle can contain from 0 to 255 characters.

bMainTitle [A60]
bMainTitle: The cell below contains the main title for the b vs Time chart. bMainTitle can contain from 0 to 255 characters.

bLegend [A62]
bLegend: The cell below specifies whether or not the Legend is to be displayed on the b vs Time chart. Enter "y" if it is or "n" if not.

In worksheet column B:
IncludeDataSet_1 [B1]
IncludeDataSet_1: The cell below specifies whether or not DataSet_1 (in columns R and S) is to be used with the parameters in this column and its curve included on the charts. Enter "y" if it is or "n" if not.

LSInit_1 [B3]
LSInit_1: The cell below contains an initial guess used to compute the Log Survival function for DataSet_1. LSInit_1 must be less than 0.

n_1 [B5]
n_1: The cell below contains a shape factor of the Weibullian description of the isothermal survival curve for Temp vs Time DataSet_1, Log S_1(Time_1) = -b_1(Temp_1)*Time_1^n_1. n_1 must be greater than 0.

k_1 [B7]
k_1: The cell below contains a coefficient in the DataSet_1 temperature dependence of the Model_1 Weibullian rate parameter, b_1(Temp_1) = LN(1+EXP(k_1*(Temp_1-TempCrit_1))). k_1 must be greater than 0.

TempCrit_1 [B9]
TempCrit_1: The cell below contains the Critical Temperature (in degrees C) used as a coefficient in the DataSet_1 temperature dependence of the Weibullian rate parameter, b_1(Temp_1) = LN(1+EXP(k_1*(Temp_1-TempCrit_1))). TempCrit_1 must be greater than 0 and greater than or equal to TempInit_1.

TempIsoRef_1 [B11]
TempIsoRef_1:The cell below contains the DataSet_1 Isothermal Reference Temperature (in degrees C). TempIsoRef_1 must be > 0.

-------------------------------
TempLineStyle_1 [B14]
TempLineStyle_1: The cell below contains an index for the LineStyle of the DataSet_1 curve on the Temp vs Time chart. TempLineStyle_1 may be either 1=Continuous, 2=Dash, 3=Dot, 4=DashDot or 5=DashDotDot.

TempLineWeight_1 [B16]
TempLineWeight_1: The cell below contains an index for the LineWeight of the DataSet_1 curve on the Temp vs Time chart. TempLineWeight_1 may be either 1=Thin, 2=Medium or 3=Heavy.

TempLineColor_1 [B18]
TempLineColor_1: The cell below contains an index for the LineColor of the DataSet_1 curve on the Temp vs Time chart. TempLineColor_1 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.

LSLineStyle_1 [B20]
LSLineStyle_1: The cell below contains an index for the LineStyle of the DataSet_1 curve on the LS vs Time chart. LSLineStyle_1 may be either 1=Continuous, 2=Dash, 3=Dot, 4=DashDot or 5=DashDotDot.

LSLineWeight_1 [B22]
LSLineWeight_1: The cell below contains an index for the LineWeight of the DataSet_1 curve on the LS vs Time chart. LSLineWeight_1 may be either 1=Thin, 2=Medium or 3=Heavy.

LSLineColor_1 [B24]
LSLineColor_1: The cell below contains an index for the LineColor of the DataSet_1 curve on the LS vs Time chart. LSLineColor_1 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.

EqTimeLineStyle_1 [B26]
EqTimeLineStyle_1: The cell below contains an index for the LineStyle of the DataSet_1 curve on the EqTime vs Time chart. EqTimeLineStyle_1 may be either 1=Continuous, 2=Dash, 3=Dot, 4=DashDot or 5=DashDotDot.

EqTimeLineWeight_1 [B28]
EqTimeLineWeight_1: The cell below contains an index for the LineWeight of the DataSet_1 curve on the EqTime vs Time chart. EqTimeLineWeight_1 may be either 1=Thin, 2=Medium or 3=Heavy.

EqTimeLineColor_1 [B30]
EqTimeLineColor_1: The cell below contains an index for the LineColor of the DataSet_1 curve on the EqTime vs Time chart. EqTimeLineColor_1 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.

bLineStyle_1 [B32]
bLineStyle_1: The cell below contains an index for the LineStyle of the DataSet_1 curve on the b vs Time chart. bLineStyle_1 may be either 1=Continuous, 2=Dash, 3=Dot, 4=DashDot or 5=DashDotDot.

bLineWeight_1 [B34]
bLineWeight_1: The cell below contains an index for the LineWeight of the DataSet_1 curve on the b vs Time chart. bLineWeight_1 may be either 1=Thin, 2=Medium or 3=Heavy.

bLineColor_1 [B36]
bLineColor_1: The cell below contains an index for the LineColor of the DataSet_1 curve on the b vs Time chart. bLineColor_1 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.

==============================
nPts_1 [B39]
nPts_1: The cell below counts the number of values (rows) of Time_1 entered by the user in column R which must also equal the number of Temperature_1 values in column S. This determines the number of solution points for DataSet_1 to be calculated and saved in columns T through V and plotted on all the charts.

EqnRoot_1 [B41]
EqnRoot_1: The cell below contains the expresson of the DataSet_1 Log Survival equation and displays the root of that equation at the current Time step. At each Time step the Solve macro uses Excel's iterative Goal Seek command to drive the EqnRoot_1 value to zero by changing the value in the LSCurr_1 cell. Computing all the LSCurr values is the main goal of this workbook. How close EqnRoot_1 comes to zero is controlled by the value assigned in the Clear macro to Application.MaxChange.

LSCurr_1 [B43]
LSCurr_1: The cell below contains the DataSet_1 Log Survival solution from the current Time step. Before iteration begins LSCurr_1 is set to LSInit_1 and thereafter LSCurr_1 is repeatedly updated by the Solve macro as it calculates and its value is copied to the appropriate row in the LS_1(Time_1) column at the end of each iteration. Computing all the LSCurr values is the main goal of this workbook.

LSPrev_1 [B45]
LSPrev_1: The cell below contains the DataSet_1 Log Survival solution from the previous Time step. Before iteration begins LSPrev_1 is set to LSInit_1 and thereafter it is repeatedly updated by the Solve macro as it calculates.

bSum_1 [B47]
bSum_1: The cell below contains the sum of the previous and current values of the b Weibullian rate parameter for DataSet_1 found in the bPrev_1 and bCurr_1 cells. bSum_1 is repeatedly updated by the Solve macro as it calculates.

bCurr_1 [B49]
bCurr_1: The cell below contains the current b Weibullian rate parameter value for DataSet_1 computed from the expression in that cell. bCurr_1 is repeatedly updated by the Solve macro as it calculates and the value is copied to the appropriate row in the b_1(Temp_1(Time_1)) column at the end of each iteration.

bPrev_1 [B51]
bPrev_1: The cell below contains the previous b Weibullian rate parameter value for DataSet_1 computed from the expression in that cell. bPrev_1 is repeatedly updated by the Solve macro as it calculates.

TempCurr_1 [B53]
TempCurr_1: The cell below contains the DataSet_1 Temperature (in degrees C) at the current Time step. TempCurr_1 is repeatedly updated by the Solve macro as it calculates. Its value is copied from the current row of the Temp_1(Time_1) column into which the user entered measured Temperature values.

TempPrev_1 [B55]
TempPrev_1: The cell below contains the DataSet_1 Temperature (in degrees C) at the previous Time step. TempPrev_1 is repeatedly updated by the Solve macro as it calculates. Its value is copied from the previous row of the Temp_1(Time_1) column into which the user entered measured Temperature values.

TimeCurr_1 [B57]
TimeCurr_1: The cell below contains the DataSet_1 Time (in minutes) at the current solution point. TimeCurr_1 is repeatedly updated by the Solve macro as it calculates since it is copied from the current row in the Time_1 column into which the user entered measured Time values.

TimePrev_1 [B59]
TimePrev_1: The cell below contains the DataSet_1 Time (in minutes) at the previous solution point. TimePrev_1 is repeatedly updated by the Solve macro as it calculates since it is copied from the previous row in the Time_1 column into which the user entered measured Time values.

bIsotherm_1 [B61]
bIsotherm_1: The cell below contains the value of the b Weibullian rate parameter function at TempIsoRef_1, the DataSet_1 Isothermal Reference Temperature.

tIsoCurr_1 [B63]
tIsoCurr_1: The cell below contains the Equivalent Isothermal Time (in minutes) at the current Time increment for TempIsoRef_1, the DataSet_1 Isothermal Reference Temperature. tIsoCurr_1 is repeatedly updated by the Solve macro as it calculates and the value is copied to the appropriate row in the EqTime_1(Time_1) column at the end of each iteration.

tIsoPrev_1 [B65]
tIsoPrev_1: The cell below contains the Equivalent Isothermal Time (in minutes) at the previous Time increment for TempIsoRef_1, the DataSet_1 Isothermal Reference Temperature. tIsoPrev_1 is repeatedly updated by the Solve macro as it calculates.

TempInit_1 [B67]
TempInit_1: The cell below contains the user-entered DataSet_1 Initial Temperature (in degrees C) from cell S2 that will be used to compute the solution. TempInit_1 must be greater than 0.

In worksheet column R:
Time_1 [R1]
Time_1: The cells below contain user-entered Time values (in minutes) for DataSet_1 which determine the number of solution points and are also used as the x-axis values for the DataSet_1 curves in the Temp vs Time, LS vs Time, EqTime vs Time and b vs Time charts.

In worksheet column S:
Temp_1(Time_1) [S1]
Temp_1(Time_1): The cells below contain user-entered Temperature values (in degrees C), for DataSet_1 at each Time step in the solution process and are used as the y-axis values for the DataSet_1 curve in the Temp vs Time chart.

In worksheet column T:
b_1(Temp_1(Time_1)) [T1]
b_1(Temp_1(Time_1)): The cells below contain bCurr_1, the b Weibullian rate parameter value for DataSet_1, for each Time step in the solution process and are used as the y-axis values for the DataSet_1 curve in the b vs Time chart.

In worksheet column U:
LS_1(Time_1) [U1]
LS_1(Time_1): The cells below contain LSCurr_1, the current Log Survival value for DataSet_1, for each Time step in the solution process and are used as the y-axis values for the DataSet_1 curve in the LS vs Time chart. Computing all the LS values is the main goal of this workbook.

In worksheet column V:
EqTime_1(Time_1) [V1]
EqTime_1(Time_1): The cells below contain tIsoCurr_1, the current Equivalent Isothermal Time (in minutes) at TempIsoRef_1, the chosen DataSet_1 Isothermal Reference Temperature, for each Time step in the solution process and are used as the y-axis values for the DataSet_1 curve in the EqTime vs Time chart.


[Return to ] Real-Time Generation of Bacterial Spores' Survival or Bacterial Spores' Inactivation Curves During Heat Sterilization (Using Excel)

Content last updated: February 4, 2008