## Scientific, information & consultancy services for the food industry

### Science Services

DSFT has been providing science based consultancy services globally since 2002.

### Translate

Select

The Dairy Science and Food Technology (DSFT) website provides scientific and technological information, Cloud-based tools and consultancy services for food scientists and technologists working in industry and in colleges and universities. A discussion forum and interactive content through "On Line" calculators are also provided. Writing/citation resources including a Harvard-type reference wizard and a range of citation-wizards can also be accessed.

There are sections on starter cultures, probiotics, cheese science and technology, bioactive peptides, ice cream, wine making, modelling in food technology, thermal processing and modified atmosphere packaging and labelling. Some general health information including reference to allergy and food intolerance is also presented.

Note added Tuesday the 17th April 2018.

Over the next few days I intend to update the spreadsheet packages below. I will add updated spreadsheets that are easier to use and will work better with the decimal time intervals and the hundreds of temperature inputs from data loggers.

Michael Mullan

Technologists must be able to calculate the cumulative lethality of a heat process normally referred to as F,  P or PU. P or in some cases PU, is often used when pasteurization-type heat treatments are used. F will be used hereafter.

Cumulative lethality is defined using a reference temperature, e.g. 121.1 °C for an 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.

1) an Excel spreadsheet based-lethal rate calculator programmed using the trapezoid rule;

2) a more accurate and easier to use Excel workbook using both of 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 and 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. 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 also provided;

Purchase can be made securely using major debit or credit cards or PayPal. PayPal processes all financial transactions on this site. Note a PayPal account is not required to make a credit or debit card payment.

### 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 the lethal rate and temperature against time is also plotted.

Note that the line number of the last data entry point ceased at line 29. It is very easy to add an infinitive number of data entries and a second spreadsheet showing how to do this was added on the 23rd January 2013.

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.

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.

Note these spread sheets use advanced Excel programming knowledge and automatically generate data entry grids.

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.

Note this spreadsheet does not use a macro to do the complex calculations but uses a fairly complicated series of statements to generate the Simpson's coefficients, 1,2,3 or 4. This works very well when time units are integers (whole numbers) but can give errors when working with small time intervals. This spreadsheet unless modified, and signed off by the author, should not be used to calculate critical F values.

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 recording 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

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.

3.) All the spreadsheets and information sources discussed above at 1 and 2 and the author's Ebook "Thermal processing of acid fruit and vegetable products. Significant microorganisms, recommended processing time / temperatures, and public health significance of spoilage."

This package contains the spread sheets and information sources described in 1 and 2 above and offers significant savings over purchasing the individual items! The author's Ebook on thermal processing is also included. The Terms and Conditions that apply to the Ice Cream Mix Spreadsheets also apply.