Comment notes from Excel file: RealTimeSterilization5.xls

Current version: February 4, 2008

In worksheet column A:
RealTimeSterilization5.xls [A1] <--This means "RealTimeSterilization5.xls" is in cell A1. The comment note below pops up when you point to cell A1.
RealTimeSterilization5.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 simulated by having their data generated and plotted on all 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 models 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 a survival curve with this version of the program, the user needs to generate a temperature profile first by selecting a heating-cooling model and its parameters. The default model included here is for a logistic-Fermi profile having six parameters: TempInit, the initial temperature, TempTarget, the target temperature, kHeat, a heating rate parameter, TimeHeat, a heating time "location" parameter, kCool, a cooling rate parameter and TimeCool, a cooling time "location" parameter. Notice that TimeCool >> TimeHeat creates a "holding time".

The survival parameters are: n, k and TempCrit. For the spores of a C. botulinum-like organism they are: 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 IncludeModel 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 the 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:
IncludeModel_1 [B1]
IncludeModel_1: The cell below specifies whether or not data is to be generated from the Model_1 parameters in this column and its curve included on the charts. Enter "y" if it is or "n" if not.

TimeInit_1 [B3]
TimeInit_1: The cell below contains the Initial Time (in minutes) at the start of the solution for Model_1. TimeInit_1 must be greater than or equal to 0.

dt_1 [B5]
dt_1: The cell below contains the Time step or delta-t increment (in minutes) between successive iteration points of the solution and therefore between successive points on the Model_1 curve of the charts. dt_1 must be greater than 0.

TimeFinal_1 [B7]
TimeFinal_1: The cell below contains the Final Time (in minutes) at which a solution will be calculated for Model_1. TimeFinal_1 must be greater than or equal to 0 and greater than or equal to TimeInit_1.

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

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

k_1 [B13]
k_1: The cell below contains a coefficient in the 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 [B15]
TempCrit_1: The cell below contains the Critical Temperature (in degrees C) used as a coefficient in the Model_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.

TempInit_1 [B17]
TempInit_1: The cell below contains the Initial Temperature (in degrees C) of the Model_1 temperature profile at the start of the solution. TempInit_1 must be greater than 0.

TempTarget_1 [B19]
TempTarget_1: The cell below contains the Target Temperature (in degrees C) of the Model_1 temperature profile. TempTarget_1 must be greater than 0 and greater than or equal to TempInit_1.

kHeat_1 [B21]
kHeat_1: The cell below contains the Heating Rate parameter of the Model_1 temperature profile. kHeat_1 must be greater than 0.

kCool_1 [B23]
kCool_1: The cell below contains the Cooling Rate parameter of the Model_1 temperature profile. kCool_1 must be greater than 0.

TimeHeat_1 [B25]
TimeHeat_1: The cell below contains the Heating Time "Location" parameter of the Model_1 temperature profile. TimeHeat_1 must be greater than 0.

TimeCool_1 [B27]
TimeCool_1: The cell below contains the Cooling Time "Location" parameter of the Model_1 temperature profile. TimeCool_1 must be greater than 0.

TempWater_1 [B29]
TempWater_1: The cell below contains the Model_1 Cooling Water Temperature (in degrees C). TempWater_1 must be < TempTarget_1.

DeltaTempWater_1 [B31]
DeltaTempWater_1: The cell below contains the Model_1 Product Temperature (in degrees C) above that of the cooling water. DeltaTempWater_1 must be > 0.

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

-------------------------------
TempLineStyle_1 [B36]
TempLineStyle_1: The cell below contains an index for the LineStyle of the Model_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 [B38]
TempLineWeight_1: The cell below contains an index for the LineWeight of the Model_1 curve on the Temp vs Time chart. TempLineWeight_1 may be either 1=Thin, 2=Medium or 3=Heavy.

TempLineColor_1 [B40]
TempLineColor_1: The cell below contains an index for the LineColor of the Model_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 [B42]
LSLineStyle_1: The cell below contains an index for the LineStyle of the Model_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 [B44]
LSLineWeight_1: The cell below contains an index for the LineWeight of the Model_1 curve on the LS vs Time chart. LSLineWeight_1 may be either 1=Thin, 2=Medium or 3=Heavy.

LSLineColor_1 [B46]
LSLineColor_1: The cell below contains an index for the LineColor of the Model_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 [B48]
EqTimeLineStyle_1: The cell below contains an index for the LineStyle of the Model_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 [B50]
EqTimeLineWeight_1: The cell below contains an index for the LineWeight of the Model_1 curve on the EqTime vs Time chart. EqTimeLineWeight_1 may be either 1=Thin, 2=Medium or 3=Heavy.

EqTimeLineColor_1 [B52]
EqTimeLineColor_1: The cell below contains an index for the LineColor of the Model_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 [B54]
bLineStyle_1: The cell below contains an index for the LineStyle of the Model_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 [B56]
bLineWeight_1: The cell below contains an index for the LineWeight of the Model_1 curve on the b vs Time chart. bLineWeight_1 may be either 1=Thin, 2=Medium or 3=Heavy.

bLineColor_1 [B58]
bLineColor_1: The cell below contains an index for the LineColor of the Model_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 [B61]
nPts_1: The cell below contains the number of solution points (rows) for Model_1 to be generated, stored in columns R through V and plotted.

EqnRoot_1 [B63]
EqnRoot_1: The cell below contains the expresson of the Model_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 comes to zero is controlled by the value assigned in the Clear macro to Application.MaxChange.

LSCurr_1 [B65]
LSCurr_1: The cell below contains the Model_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 [B67]
LSPrev_1: The cell below contains the Model_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 [B69]
bSum_1: The cell below contains the sum of the previous and current values of the b Weibullian rate parameter for Model_1 found in the bPrev_1 and bCurr_1 cells. bSum_1 is repeatedly updated by the Solve macro as it calculates.

bCurr_1 [B71]
bCurr_1: The cell below contains the current b Weibullian rate parameter value for Model_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 [B73]
bPrev_1: The cell below contains the previous b Weibullian rate parameter value for Model_1 computed from the expression in that cell. bPrev_1 is repeatedly updated by the Solve macro as it calculates.

TempCurr_1 [B75]
TempCurr_1: The cell below contains the current Model_1 Temperature (in degrees C) computed from the temperature profile expression in that cell. TempCurr_1 is repeatedly updated by the Solve macro as it calculates and the value is copied to the appropriate row in the Temp_1(Time_1) column at the end of each iteration.

TempPrev_1 [B77]
TempPrev_1: The cell below contains the previous Model_1 Temperature (in degrees C) computed from the temperature profile expression in that cell. TempPrev_1 is repeatedly updated by the Solve macro as it calculates.

TimeCurr_1 [B79]
TimeCurr_1: The cell below contains the Time (in minutes) at the current Model_1 solution point. TimeCurr_1 is repeatedly updated by the Solve macro as it calculates and the value is copied to the appropriate row in the Time_1 column at the end of each iteration.

TimePrev_1 [B81]
TimePrev_1: The cell below contains the Time (in minutes) at the previous Model_1 solution point. TimePrev_1 is repeatedly updated by the Solve macro as it calculates.

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

tIsoCurr_1 [B85]
tIsoCurr_1: The cell below contains the Equivalent Isothermal Time (in minutes) at the current Time increment for TempIsoRef_1, the Model_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 [B87]
tIsoPrev_1: The cell below contains the Equivalent Isothermal Time (in minutes) at the previous Time increment for TempIsoRef_1, the Model_1 Isothermal Reference Temperature. tIsoPrev_1 is repeatedly updated by the Solve macro as it calculates.

In worksheet column R:
Time_1 [R1]
Time_1: The cells below contain TimeCurr_1, the current Model_1 Time (in minutes), for each step in the solution process and are used as the x-axis values for the Model_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 TempCurr_1, the current Model_1 Temperature (in degrees C), for each Time step in the solution process and are used as the y-axis values for the Model_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 Model_1 b Weibullian rate parameter value for each Time step in the solution process and are used as the y-axis values for the Model_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 Model_1 Log Survival value, for each Time step in the solution process and are used as the y-axis values for the Model_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 Model_1 Isothermal Reference Temperature, for each Time step in the solution process and are used as the y-axis values for the Model_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