Site Coordinator | Contributors | Links | Contribute an article or link | News | Forum |
MICROSOFT EXCEL LETHAL RATE CALCULATORS
Technologists must be able to calculate the cumulate lethality of a heat process normally referred to as F. This is done by defining a reference temperature, e.g. 121.1 °C for a F0 calculation using a low acid food or e.g. 93.3 °C for an acid product, at which the equivalent lethal effects experienced during heating and cooling at lower temperatures are calculated.
Calculation of the F value is subject to a range of errors including the error associated with determining the area under the lethality / time curve.
The area under the lethality curve is normally calculated using numerical integration. The most commonly used method is the trapezium or trapezoid method. An alternative, more accurate, but slightly more complicated method, is to use Simpson's rule or to be more correct Simpson's rules.
Site users can choose to download 1) an Excel spreadsheet based-lethal rate calculator programmed using the trapezoid rule or 2) a more accurate Excel workbook using both Simpson's rules and also offering the option of using the trapezoid rule. This download also contains a spreadsheet to convert Z-values in Fahrenheit or Celsius to the desired measurement system.
Options 1 and 2 are available as immediate downloads.
1.) Trapezium or trapezoid based Excel spreadsheet for calculating F values
The lethal rate calculator used on this site was validated using this Microsoft Excel spreadsheet (figure 1).
Figure 1. Screen shot of a Microsoft Excel spreadsheet for calculating F values of thermal processes
The spreadsheet converts temperature to lethal rate and plots lethal rate and temperature against time. The trapezoid rule is used to calculate the area under the curve and depending on the reference temperature chosen Fo or other F value can be calculated. A graph showing lethal rate and temperature against time is also plotted.
The download contains a Microsoft Excel spreadsheet and a document in PDF form. The PDF file explains the basis of lethal rate calculation and also how to use Microsoft Excel to calculate the area under a curve using the trapezoid rule.
All the cell formulas are unlocked. The spreadsheet is for your own personal educational use. The Terms and Conditions that apply to the Ice Cream Mix Spreadsheets also apply.
 |
Following secure payment you will shortly receive an Email with a download link. The link is valid for 7-days and during this time you can download the spreadsheet 4-times.
NOTE!! Please change the SPAM settings on your Email software so that the Email address michael.mullan at dairyscience.info (replace the at with a @ symbol) is approved otherwise your Anti-spam software may reject the Email with the download link!
2.) Microsoft Excel workbook F calculator programmed using Simpson's rules and a worksheet programmed using the Trapezoid method for comparative analysis.
The workbook contains two worksheets and the details of the trapezoid sheet are shown in (Figure 2).
Figure 2. Screen shot of the updated trapezoid spread sheet for calculating F values of thermal processes available with option 2.
The cells coloured yellow are the input cells and the user can set the temperature interval between readings e.g. 0.5° C, the duration of the thermal process, the Z value and the reference temperature. Once the temperature interval and duration of the process are entered a grid will be generated to enable temperature values to be entered; conditional statements are used to generate this grid automatically.
There is also a facility to calculate
the number of log reductions of a target microbial group at the reference
temperature. To use this facility the user simply enters the corresponding D
value at the reference temperature. Note, it is not essential to enter the
D-value to calculate the F value. The D value is only required if the user
needs to calculate the number of log reductions of a microbial group or
organism.
The worksheet programmed using Simpson's rules is shown in Figure 3.
Figure 3. Screen shot of the spread sheet programmed using Simpson's rules for calculating F values of thermal processes.
Simpson's one third rule is used to calculate area when the an odd number of data points are used and Simpson's three eights rule is used when working with an even number of data points. The spreadsheet undertakes these calculations automatically.
Again there is an option to calculate the log reduction in the target microbial group following heat processing.
As with both worksheets a message (figure 4) is given if no D-value is entered. The code for this message which is entered into cell C10 is given and can be altered by the user.
Figure 4. How to change the message shown when a value for D is not entered.
Both worksheets have been configured to start recoding time at value 0. This can be changed to what ever value you require. After this has been done, the following values will increase by the time interval set.
The code used to set the initial time interval is entered into cell A15 and is shown in figure 6.
Figure 5. How to change initial time value shown on the option 2 spreadsheets.
Most users will not need to make these changes; this information is provided for advanced Excel users only and should work with most versions of Excel. To change the value from 0, simply replace the 0 with an appropriate number say 3 e.g. =IF(MXTF>0,3,"").
To enable users who prefer to work in Fahrenheit a zipped file with an adapted workbook is also provided.
I frequently get queries asking how to convert Z values from Fahrenheit to Celsius and vice versa and a Z-value converter is also enclosed in the zip file (Figure 6).
Figure 6. Screenshot of Z value converter.
A PDF document listing D, F, Z values and reference temperatures for the major spoilage organisms of significance to processing low acid and acid foods is also provided. Additionally a link to a free On Line resource that details thermal resistance data for many spoilage and pathogenic organisms significant to the food and pharmaceutical industries is provided.
|
|
Donate £19.99 to obtain the Simpson F calculator spreadsheet:package
Following secure payment you will shortly receive an
Email with a download link. The link is valid for 7-days and during this
time you can download the spreadsheet 4-times.
All the cell formulas are unlocked. The spreadsheet is for your own personal educational use. The Terms and Conditions that apply to the Ice Cream Mix Spreadsheets also apply.
NOTE!! Please change the SPAM settings on your Email software so that the Email address michael.mullan at dairyscience.info (replace the at with a @ symbol) is approved otherwise your Anti-spam software may reject the Email with the download link!
Questions?