MICROSOFT EXCEL LETHAL RATE CALCULATORS AND TEMPERATURE TIME INTEGRATORS FOR THERMAL PROCESSES
This section provides the context to using Excel to calculate the cumulative lethal effects (at all stages during processing) of heat on microorganisms and provides an explanation of how the Excel spreadsheets and On Line calculators available for download from the Dairy Science and Food Technology (DSFT) work.
Here we provide an overview of the background, including a summary of the underlying mathematics, required to produce an Excel spreadsheet for performing basic thermal processing calculations. Note I am not providing a guide to using spreadsheets but basic information that a competent Excel user should be able to use to make their own thermal processing spreadsheet.
All thermal process calculations start with a target microorganism. This is usually a bacterium or spore, selected because it is a pathogen or spoilage organism. The organism is designated by its Z-value and its reference temperature, Tref. Clostridium botulinum has two variants that must be considered differently in heat treatment processes. The non-proteolytic variant that grows at low temperatures produces spores of relatively low resistance to heat whereas the variant concerned with canning low acid food such as meats and vegetables, produces much more heat resistant spores. Spores of this variant have a Z-value of 10 ℃, and a Tref of 121.1 ℃.
Some DSFT spreadsheets also calculate the concentration of chemical compounds produced as a result of high temperature processing known as thermal process indicators or TTI.
How do the spreadsheets and the free On Line thermal process calculators work on the Dairy Science and Food Technology website?
The calculated cumulative lethality at the Tref is known as the F value. This takes account of the lethal effects during heating, holding at the target processing temperature (this can be lower or higher than Tref) and during cooling. The F value obtained using a Z-value of 10 ℃, and a Tref of 121.1 ℃ is known as F0. Note when temperatures lower than 100°C are used (pasteurising temperatures) P or PU may be used instead of F value. This can be confusing and F value has been used throughout this website to denote the cumulative effects of heat on lethality.
F values tend to be used up to 121.1 ℃. In UHT-processing, temperatures up to 150 ℃ may be used and while F values e.g. F0 must be calculated in some jurisdictions, it is increasing common to use B* and C* as indicators of the bacteriological effects of heat against thermophilic spore formers and the potential adverse chemical effects respectively. B* and C* are discussed further in the article on UHT-processing.
To determine the F value we first calculate the lethal effects of heat at the temperatures used during the heating process. Lethal rate is calculated using equation 1.
Equation 1. L= 10 (T-Tref/Z).
Where L is the lethal rate or lethality, T is the temperature, in Celsius or in Fahrenheit in the US, at which the lethal rate is required and Tref is the reference temperature.
Note lethal rate is a relative term that compares the microbial killing effect at a measured temperature to one minute at the reference temperature.
Tref will vary depending on whether Fo is being calculated or whether a pasteurisation process or other heat treatment is being assessed. A Tref of 121.1°C or 250°F is used in the determination of Fo. If F90°C or other F value is required then Tref must be set to this temperature on the spreadsheet or On Line calculator.
The Z-value, measured in °C or °F can be defined in several ways. The formal way is to describe it as the reciprocal of the slope of the thermal death curve for the target microorganism or spore; 10° C is the value frequently used in Fo calculations performed on low acid foods. An alternative explanation and perhaps one that is easier to understand, and apply, is that Z is a value expressing the increase in temperature necessary to obtain the same lethal effect in 1/10 of the time. Note it is the decimal reduction value D that is a measure of the resistance of a microorganism to heat. The Z-value provides information on how this changes with temperature.
Spreadsheets available from DSFT, with the exception of some demonstration spreadsheets, allow users to vary the Z-value and the Tref depending on the target organism being considered. The Z-value has a significant effect on the F value of a process.
The F value is calculated by determining L at appropriate points from the time vs lethality curve and integrating the time and L values using numerical integration to obtain the area under the time–lethality curve.
Numerical integration is used to evaluate a definitive integral when the explicit function or equation is not known and the lethal rates (or the temperatures used to derive them) are available in tabular form e.g. on an Excel spreadsheet.
In thermal processing calculations, numerical integration is used to find the area under the lethal rate versus time curve. Two main methods are used, namely the Trapezoid rule and Simpson’s rules. The Trapezoid rule is the simplest to understand and implement and despite it being less accurate is generally the method used to calculate F values in industry.
There are several excellent textbooks and websites that explain numerical integration. Liengme (2007) and Billo (2007) have authored books on Excel that explain numerical integration well and are particularly relevant to scientists and engineers. The website My Engineering World by Christos Samaras discusses how to determine the area under a curve.
In thermal processing graphs of time versus lethality are plotted. The values on the X-axis (horizontal) represent time and the vertical values on the Y-axis represent lethal rate. The area under the curve can be divided into a series of equally-spaced trapezoids (Figure 1). The area of trapezoid A1 is given by equation 2.
Equation 2. A1 = DX (Yi + Yi+1
Where A1 is the area of the trapezoid and DX is the time interval between the values Yi + Yi+1.
If we were to convert all the segments to trapezoids, determine their individual areas then the area under the curve would be approximately equal to the sum of the individual trapezoids.
The Trapezoid rule as with other methods of numerical integration approximates the area under a curve. If you look closely at A1 and at the segment of the curve bounded by Yi to Yi+1 you will note the error introduced by the method in this case, the trapezoid A1 over estimates the area. However if we draw a trapezoid to the right of this, A2, the area of this trapezoid is smaller than the actual segment and hence this gives an underestimate of the actual area.
A more accurate estimate of the area under a curve can be obtained using one of Simpson’s rules, namely, Simpson’s 1/3 and / or Simpson’s 3/8 rules.
A detailed explanation of Simpson’s rules requires a much greater understanding of mathematics than that required to understand and use the Trapezoid rule and may explain why the less accurate Trapezoid rule is more widely used.
There is helpful article on Wikipedia (Wikipedia, 2018) that explains the derivation of Simpson’s rules and their application. The authors include Figure 2 in their explanation.
Figure 2. The authors in Wikipedia (2018) explain that Simpson's rule can be derived by approximating the integrand f (x) (in blue) by the quadratic interpolant P(x) (in red).
How to calculate F values using an Excel spreadsheet?
The time temperature results from a heat treatment process are given in Table 1.
Table 1. Time-temperature readings from thermal treatment process
How do we process these values to obtain a value for F0 for this heat treatment? There are several ways of implementing the Trapezoid Rule. One way is to create 4 columns in Excel as indicated in Table 2.
Table 2. Excel spreadsheet for calculating F0
Lethality is calculated using equation 1 by entering the formula “=10^((B4-121.1)/10)” (remove the “”) at cell C3 and copying this down to C17. The lethal rates corresponding to each temperature are given.
The area of each trapezoid is then calculated. The formula “=(C4+C5)/2*(A5-A4)” is entered into cell D4 and copied down to D17. Finally the area of each trapezoid is summed at cell D19 to give the approximate area under the curve, F0, = 2.74.
The Trapezoid rule can also be implemented using 1) coefficients, which are slightly easier to use than the previous method, 2) a dedicated Excel function named SUMPRODUCT which is much easier to apply and 3) simplest of all by using a custom VBA function, also called an Excel macro. These custom functions must be specially written for particular applications. The authors of both text books previously mentioned provide free basic macros and Christos Samaras has provided an excellent free macro on his blog that unlike the ones in the textbooks provides basic error checking and an explanation of how the code works.
If the Trapezoid rule gives an F0 = 2.74 what value do you get using Simpsons rules? Using Simpson’s 1/3 Rule a value of F0 of 2.895 is obtained (Table 3).
Table 3. Excel spreadsheet for calculating F0 using Simpson’s 1/3 Rule
These calculations can be checked using the Free On Line calculator on the Dairy Science and food Technology website.
Discrepancies between the two methods of calculation are more common than is usually realised. In most instances Simpson’s rules give the more accurate result.
A screenshot of the results of the calculation and the lethality time plot for the data in Table 1 (Figure 3) reveals that the curve is not symmetrical and this is one of the situations where marked differences between the two methods can be seen.
Figure 3. A screen shot of the data in Table 1 inputted to the Free On Line calculator on the Dairy Science and food Technology website.
Some consideration should be given to data validation especially if other people might be using the spreadsheet. Most spreadsheets offer a range of data validation inputs for non-programmers, this facility can be accessed under the Data tab in Excel. Using validation you might wish to check input data for blank cells, text and negative numbers for example.
Macros can also be written to do this e.g. the following portion of a simple macro checks if cells in a previously defined range contain numbers and if not (contains text) will give an alert and change the cell colour to red.
Ensure values are NOT letters
If Not IsNumeric(Cell.Value) Then
MsgBox ("Values can only be numeric." & vbNewLine & _
"Change '" & Cell.Value & "' in " & Cell.Address & " to a numeric value.")
Cell.Interior.Color = RGB(255, 0, 0)
It might also be useful to define the minimum number of time-temperature values before the calculation can proceed. Again, this is easily done using a macro.
Billo, E.J. (2007). Excel for Scientists and Engineers – Numerical Methods. John Wiley and Sons, Inc.
Liengme, B.V. and Ellert, D.J. (2007). A Guide to Microsoft Excel 2007 for Scientists and Engineers. Elsevier Ltd.
Samaras, C. (2013). Numerical Integration in Excel Using the Trapezoidal Rule. Available from: https://www.myengineeringworld.net/2013/06/integration-in-excel-trapezoidal-rule.htmll. Accessed 20 May 2018.
Wikipedia (2018). Simpson's rule [online]. Available from: <https://en.wikipedia.org/wiki/Simpson's_rule>. Accessed 2 May 2018
How to cite this article
Mullan, W.M.A. (2018). [On-line]. Available from: https://www.dairyscience.info/thermal-processing/304-excel-spreadsheets.html . Accessed: 1 March, 2024.