Comment notes from Excel file: RealTimeSterilizationData.xls

Current version: February 4, 2008

In worksheet column A:
RealTimeSterilizationData.xls [A1] <--This means "RealTimeSterilizationData.xls" is in cell A1. The comment note below pops up when you point to cell A1.
RealTimeSterilizationData.xls: This Excel workbook simulates the diminishing survival ratio of targeted bacterial spores during a heat process intended for their elimination in real time. 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).

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 from measured time and temperature data, the user needs to enter Time values (in minutes) into worksheet column D starting at cell D2 and Temperature values (in degrees C) corresponding to each Time value into worksheet column E starting at cell E2.

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 chart settings in column A.

Enter an initial value for each model variable in column B. The pop-up comment note that appears when the name cell above a variable's cell is pointed to with the mouse 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 in column C, clear the graphical data columns F through H 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 calculation cells in column C and the graphical data cells in columns F through H. 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 and C are assigned names by the Init function. Those names are then used in the formulas in column C 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 can be greatly improved (by 20 times or more) by changing Application.ScreenUpdating from True to False in the Solve macro. In that case, however, you will not be able to 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, you cannot 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 should be made 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 the 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 the 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 the charts. TimeAxisTitle can contain from 0 to 255 characters.

TempAxisMin [A20]
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 [A22]
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 [A24]
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 [A26]
TempMainTitle: The cell below contains the main title for the Temp vs Time chart. TempMainTitle can contain from 0 to 255 characters.

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

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

TempLineColor [A32]
TempLineColor: The cell below contains an index for the LineColor of the Temp vs Time curve. TempLineColor 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.

LSAxisMin [A34]
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 [A36]
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 [A38]
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 [A40]
LSMainTitle: The cell below contains the main title for the LS vs Time chart. LSMainTitle can contain from 0 to 255 characters.

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

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

LSLineColor [A46]
LSLineColor: The cell below contains an index for the LineColor of the LS vs Time curve. LSLineColor 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.

EqTimeAxisMin [A48]
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 [A50]
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 [A52]
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 [A54]
EqTimeMainTitle: The cell below contains the main title for the EqTime vs Time chart. EqTimeMainTitle can contain from 0 to 255 characters.

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

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

EqTimeLineColor [A60]
EqTimeLineColor: The cell below contains an index for the LineColor of the EqTime vs Time curve. EqTimeLineColor 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.

bAxisMin [A62]
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 [A64]
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 [A66]
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 [A68]
bMainTitle: The cell below contains the main title for the b vs Time chart. bMainTitle can contain from 0 to 255 characters.

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

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

bLineColor [A74]
bLineColor: The cell below contains an index for the LineColor of the b vs Time curve. bLineColor 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 worksheet column B:
LSInit [B4]
LSInit: The cell below contains an initial guess used to compute the Log Survival function. LSInit must be less than 0.

n [B7]
n: The cell below contains a shape factor of the Weibullian description of the isothermal survival curve, Log S(Time) = -b(Temp)*Time^n. n must be greater than 0.

k [B10]
k: The cell below contains a coefficient in the temperature dependence of the Weibullian rate parameter, b(Temp) = LN(1+EXP(k*(Temp-TempCrit))). k must be greater than 0.

TempCrit [B13]
TempCrit: The cell below contains the Critical Temperature (in degrees C) used as a coefficient in the temperature dependence of the Weibullian rate parameter, b(Temp) = LN(1+EXP(k*(Temp-TempCrit))). TempCrit must be greater than 0 and greater than or equal to TempInit.

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

In worksheet column C:
nPts [C1]
nPts: The cell below counts the number of values (rows) of Time entered by the user in column D which must also equal the number of Temperature values in column E. This determines the number of solution points to be calculated and saved in columns F through H and plotted on all the charts.

EqnRoot [C4]
EqnRoot: The cell below contains the expresson of the 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 value to zero by changing the value in the LSCurr cell. Computing 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 [C7]
LSCurr: The cell below contains the Log Survival solution from the current Time step. Before iteration begins LSCurr is set to LSInit and thereafter LSCurr is repeatedly updated by the Solve macro as it calculates and its value is copied to the appropriate row in the LS(Time) column at the end of each iteration. Computing the LSCurr values is the main goal of this workbook.

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

bSum [C13]
bSum: The cell below contains the sum of the previous and current values of the b Weibullian rate parameter found in the bPrev and bCurr cells. bSum is repeatedly updated by the Solve macro as it calculates.

bCurr [C16]
bCurr: The cell below contains the current b Weibullian rate parameter value computed from the expression in that cell. bCurr is repeatedly updated by the Solve macro as it calculates and the value is copied to the appropriate row in the b(Temp(Time)) column at the end of each iteration.

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

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

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

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

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

bIsotherm [C34]
bIsotherm: The cell below contains the value of the b Weibullian rate parameter function at TempIsoRef, the Isothermal Reference Temperature.

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

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

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

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

In worksheet column E:
Temp(Time) [E1]
Temp(Time): The cells below contain user-entered Temperature values (in degrees C) for each Time step in the solution process and are used as the y-axis values in the Temp vs Time chart.

In worksheet column F:
b(Temp(Time)) [F1]
b(Temp(Time)): The cells below contain bCurr, the b Weibullian rate parameter value, for each Time step in the solution process and are used as the y-axis values in the b vs Time chart.

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

In worksheet column H:
EqTime(Time) [H1]
EqTime(Time): The cells below contain tIsoCurr, the current Equivalent Isothermal Time (in minutes) at TempIsoRef, the chosen Isothermal Reference Temperature, for each Time step in the solution process and are used as the y-axis values 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