## 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.

Content last updated: February 4, 2008