Dairy Science and Food Technology

Scientific, information & consultancy services for the food industry

Copyright Protected

Content copyright protected

Science Services

DSFT has been providing science based consultancy services globally since 2002.
  
Click to learn more.

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.

DOWNLOAD MICROSOFT EXCEL LETHAL RATE CALCULATORS

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.

Site users can choose to download:

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;

3) all the spreadsheets above 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."

Options 1 to 3 are available as immediate downloads.

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.

NOTE!! Please change the SPAM settings on your Email software so that Email from dairyscience.info is approved otherwise your anti-spam software may reject your download link!

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

Graphic of Microsoft Excel spreadsheet for calculating Fo
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.

 
Visa MasterCard Discover American Express


Donate £9.99 to download the  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.

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

F calculator programmed using Simpson's rules

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.

 

Worksheet programmed using Simpson's rules

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.

Code error

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.

Spreadsheet screen shot

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

Z-converter

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


Visa MasterCard Discover American Express

 Donate £40.00 to download 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.


 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.

 


Visa MasterCard Discover American Express


Donate £60.00 to download the thermal processing Ebook and spreadsheet package.
 

 If you have questions, please contact us and we will respond, normally within one working day.

We use cookies to improve our website and your experience when using it. To find out more about the cookies we use, see our Privacy and Cookie Policy.

'Learn more about managing cookies'