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

Content last updated: February 4, 2008