Comment Notes from Excel File: MicrobialBiphasicInactivation.xls

Comment Notes from Excel® File: MicrobialBiphasicInactivation.xls

Current version: February 4, 2008

In worksheet column A:
MicrobialBiphasicInactivation.xls [A1] <--This means "MicrobialBiphasicInactivation.xls" is in cell A1. Below is the comment note that pops up when you point to cell A1.
MicrobialBiphasicInactivation.xls: This Excel workbook simulates the logarithmic inactivation of a targeted microorganism during non-isothermal heat treatment. This version of the program simulates the inactivation of organisms whose isothermal survival curve follows the model LS(t) = If[Time<=t1, -k1*Time, -k1*t1-k2*(Time-t1)]. The temperature dependence of the inactivation parameters t1(Temp), k1(Temp) and k2(Temp) obeys a logistic relationship. Explanation of the model can be found in Corradini, M.G, Normand, M.D. and Peleg, M. (see References).

For more information on the concept, its various applications in calculating microbial inactivation during heating at variable temperatures and its implementation with more complicated inactivation models, contact the authors.

Background [A2]
Background: For generating an inactivation curve with this version of the program, the user needs to generate a temperature profile first by selecting a temperature model and its parameters. The default model included here, Temp(time)=TempInit + time/(Tpar1 + Tpar2*time), has three parameters: TempInit, the initial temperature, Tpar1, a rate parameter and Tpar2, the inverse of the asymptotic temperature value.

The inactivation parameters are: t1par1, t1par2, k1par1, k1par2, k2par1 and k2par2 (e.g., for Salmonella enteritidis, they are: t1par1=1.02291, t1par2=54.9275, k1par1=0.64394, k1par2=64.7833, k2par1=0.606047, k2par2=67.4741).

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 D through F 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 inactivation equation and update the model and graphical data cells in columns C through F. It will also draw two charts: the Temperature vs Time and Inactivation equation LogS vs Time.

CAUTION: Some combinations of temperature profile and survival parameters with the chosen time interval (dt) may create a very steep survival curve which will result in a program error.

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 workbook 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 Excel 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., Corradini, M.G. and Normand, M.D. 2004 Kinetic models of complex biochemical reactions and biological processes. Chemie Ingenieur Technik 76:413-423.

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.

Corradini, M.G., Normand, M.D. and Peleg, M. Modeling non-isothermal heat inactivation of microorganisms having biphasic isothermal survival curves. Intnl. J. Food Microbiol. 116:391-399.

Model by: Maria G. Corradini, PhD. and Professor Micha Peleg.
Program by: Mark D. Normand, EDP Programmer II and Maria G. Corradini, PhD.
Peleg & Normand located 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/
Email (Corradini): mariagcorradini@gmail.com

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 width of each bin in the histogram of the risk estimation data in column P.

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.

LogSAxisMin [A34]
LogSAxisMin: The cell below contains the minimum value for the LogS (Inactivation) axis on the LogS vs Time chart. LogSAxisMin must be less than 0 and less than LogSAxisMax.

LogSAxisMax [A36]
LogSAxisMax: The cell below contains the maximum value for the LogS (Inactivation) axis on the LogS vs Time chart. LogSAxisMax must be less than or equal to 0.

LogSAxisTitle [A38]
LogSAxisTitle: The cell below contains the title for the LogS (Inactivation) axis on the LogS vs Time chart. LogSAxisTitle can contain from 0 to 255 characters.

LogSMainTitle [A40]
LogSMainTitle: The cell below contains the main title for the LogS vs Time chart. LogSMainTitle can contain from 0 to 255 characters.

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

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

LogSLineColor [A46]
LogSLineColor: The cell below contains an index for the LineColor of the LogS vs Time curve. LogSLineColor 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:
TimeInit [B1]
TimeInit: The cell below contains the Initial Time (in minutes) at the start of the solution. TimeInit must be greater than or equal to 0.

dt [B4]
dt: 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 charts. dt must be greater than 0 and less than or equal to 0.5.

CAUTION: Some combinations of temperature profile and survival parameters with the chosen time interval (dt) may create a very steep survival curve which will result in a program error.

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

LogSInit [B10]
LogSInit: The cell below contains an initial guess used to compute the LogS inactivation function. LogSInit must be greater than 0.

t1par1 [A13]
t1par1: The cell below contains the rate coefficient of the model that describes the temperature dependence of the parameter t1(Temp)=1+(100-1)/(1+Exp(t1par1*(Temp-t1par2))). t1par1 must be greater than 0.

t1par2 [A16]
t1par2: The cell below contains the second coefficient of the model that describes the temperature dependence of the parameter t1(Temp)=1+(100-1)/(1+Exp(t1par1*(Temp-t1par2))). t1par2 must be greater than 0.

k1par1 [A19]
k1par1: The cell below contains the rate coefficient in the temperature dependence of the parameter k1(Temp)=100/(1+EXP( k1par1 * (k1par2 - Temp))). k1par1 must be greater than 0.

k1par2 [A22]
k1par2: The cell below contains the second coefficient in the temperature dependence of the parameter k1(Temp)=100/(1+EXP(k1par1 * (k1par2 * Temp))). k1par2 must be greater than 0.

k2par1 [A25]
k2par1: The cell below contains the rate coefficient in the temperature dependence of the parameter k2(Temp)=100/(1+EXP(k2par1 * (k2par2 - Temp))). k2par1 must be greater than 0.

k2par2 [A28]
k2par2: The cell below contains the second coefficient in the temperature dependence of the parameter k2(Temp)=100/(1+EXP(k2par1 * (k2par2 - Temp))). k2par2 must be greater than 0.

TempInit [A31]
TempInit: The cell below contains the Initial Temperature (in degrees C) of the temperature profile Temp(time)=TempInit+time/(Tpar1 + Tpar2*time) at the start of the solution. TempInit must be greater than 0.

Tpar1 [A34]
Tpar1: The cell below contains the rate parameter (in degrees C) of the temperature profile Temp(time)=TempInit+time/(Tpar1 + Tpar2*time). Tpar1 must be greater than 0.

Tpar2 [A37]
Tpar2: The cell below contains the inverse of the asymptotic temperature of the temperature profile Temp(time)=TempInit+time/(Tpar1 + Tpar2*time). Tpar2 must be greater than 0.

In worksheet column C:
nPts [C1]
nPts: The cell below contains the number of solution points (rows) to be generated, stored in columns D through F and plotted.

EqnRoot [C4]
EqnRoot: The cell below contains the expression of the inactivation 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 LogSCurr cell. Computing the LogSCurr 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.

LogSCurr [C7]
LogSCurr: The cell below contains the inactivation solution from the current Time step. Before iteration begins LogSCurr is set to LogSInit and thereafter LogSCurr is repeatedly updated by the Solve macro as it calculates and its value is copied to the appropriate row in the LogS(Time) column at the end of each iteration. Computing the LogSCurr values is the main goal of this workbook.

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

t1Sum [C13]
t1Sum: The cell below contains the sum of the previous and current values of the t1 parameter found in the t1Prev and t1Curr cells. t1Sum is repeatedly updated by the Solve macro as it calculates.

t1Curr [C16]
t1Curr: The cell below contains the current t1 parameter value computed from the expression in that cell. t1Curr is repeatedly updated by the Solve macro as it calculates.

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

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

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

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 and the value is copied to the appropriate row in the Time column at the end of each iteration.

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.

k1Sum [C34]
k1Sum: The cell below contains the sum of the previous and current values of the k1 parameter found in the k1Prev and k1Curr cells. k1Sum is repeatedly updated by the Solve macro as it calculates.

k1Curr [C37]
k1Curr: The cell below contains the current k1 parameter value computed from the expression in that cell. k1Curr is repeatedly updated by the Solve macro as it calculates.

k1Prev [C40]
k1Prev: The cell below contains the previous k1 parameter value computed from the expression in that cell. k1Prev is repeatedly updated by the Solve macro as it calculates.

k2Sum [C43]
k2Sum: The cell below contains the sum of the previous and current values of the k2 parameter found in the k2Prev and k2Curr cells. k2Sum is repeatedly updated by the Solve macro as it calculates.

k2Curr [C46]
k2Curr: The cell below contains the current k2 parameter value computed from the expression in that cell. k2Curr is repeatedly updated by the Solve macro as it calculates.

k2Prev [C49]
k2Prev: The cell below contains the previous k2 parameter value computed from the expression in that cell. k2Prev is repeatedly updated by the Solve macro as it calculates.

In worksheet column D:
Time [D1]
Time: The cells below contain TimeCurr, the current Time (in minutes), for each step in the solution process and are used as the x-axis values in the Temp vs Time and LogS vs Time charts.

In worksheet column E:
Temp(Time) [E1]
Temp(Time): The cells below contain TempCurr, the current Temperature (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:
LogS(Time) [F1]
LogS(Time): The cells below contain LogSCurr, the current LogS inactivation value, for each Time step in the solution process and are used as the y-axis values in the LogS vs Time plot. Computing the LogS inactivation values is the main goal of this workbook.

Simulating Non-Isothermal Biphasic Heat Inactivation of Microorganisms Using Excel

Content last updated: February 4, 2008