# Spreadsheets for calculating F, B*, C* values of thermal processes and the concentration of Time Temperature Indicators.

**Introduction **

DSFT has an extensive range of free-On Line resources for undertaking a wide range of thermal process calculations including dry heat sterilisation (F_{H}) and depyrogenation (F_{D} or F_{P}). This page provides access to a range of Microsoft Excel spreadsheets that can be downloaded for a small donation and will work on a PC or Mac without access to the Internet. In general these spreadsheets are similar to their corresponding free applications on this website.

A number of the spreadsheets have lite or demonstration versions for evaluation prior to purchase. If you want to test a version that does not offer a test version contact me and I can arrange to provide this.

These donations are important. They contribute towards the running costs of the website and enable me to provide free access to the website rather than require a subscription.

**Spreadsheets available for download**

1) An Excel spreadsheet based-lethal rate calculator programmed using the trapezoid rule and a fully functional demonstration macro-enabled spreadsheet (but limited to 8 time-temperature inputs) using Excel-macros with extensive input data checking.

2) The full version of the macro based spreadsheet described above using the trapezoid rule to determine the lethality (F, value) of a thermal process.

3) An Excel workbook using non-macro based coefficients to implement both of Simpson's rules and the trapezoid rule to calculate F values. 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.

4) A macro based spreadsheet for determining the lethality (F, value) of a thermal process using the trapezoid and Simpson's rules. This is an Excel based version of the calculator at https://www.dairyscience.info/f-s-t/ .

5) A macro based spreadsheet for determining the lethality (F, B* values) and chemical changes (C* value) for UHT processes using the trapezoid and Simpson's rules. This is an Excel based version of the calculator at https://www.dairyscience.info/uht-t/ .

6) A macro based spreadsheet for determining the lethality (F, B* values) and chemical changes (C* value, formation of: HMF, Lactulose, Furosine, and destruction of thiamine) in heated milk integrated using the trapezoid and Simpson's rules. This is an Excel based version of the calculator at https://www.dairyscience.info/uht/ .

7) The author's Ebook "Thermal processing of acid fruit and vegetable products. Significant microorganisms, recommended processing time / temperatures, and public health significance of spoilage".

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

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

## Description of spreadsheets

Note to use the spreadsheets to calculate F_{H}, a T_{ref} of 170°C and a Z value of 20ºC should be used. For F_{D} a T_{ref} of 250°C and a Z value of 46.4ºC should be used.

**1) Trapezium or trapezoid based Excel spreadsheet for calculating F values and**** a demonstration macro-based Excel spreadsheet .**

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 F_{o} 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 in Figure 1 ceased at line 29. It is very easy to add an infinitive number of data entries and instructions on how to do this are provided.*

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.

The download also contains a macro-based demonstration spreadsheet. See below (Figure 2).

*Figure 2. Image of the macro-based demonstration spreadsheet.*

This spreadsheet uses the Microsoft Visual Basic Scripting (VBScript) language to write a script for the trapezoid rule, to validate the inputs (cells must not contain text, be empty and should not contain negative numbers) including the submission of paired sets of data. Errors generate alert messages and individual cells are coloured red to enable easy resolution of input errors. Users also have the option of calculating the number of log reductions of the target microorganism by entering the D-value of the organism and clicking on the calculate button. The demonstration spreadsheet only allows 8 pairs of time-temperature values but is not otherwise restricted. A graph of time versus lethal rate is also generated.The macros are password-protected and not accessible to users.

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

** 2) The full version of the macro based spreadsheet described above using the trapezoid rule to determine the lethality (F, value) of a thermal process. **

This is similar to the demonstration spreadsheet mentioned above except that by default up to 3000 pairs of time-temperature values can be pasted or typed into the calculator (see Figure 3) and the macros are not pass-word protected. The macros are not password protected and you have full access to all the code!

Figure 3. Image of the macro-based unrestricted trapesoid spreadsheet

Donate £70 to download the macro enabled spreadsheet.

### 3) Microsoft Excel workbook F calculator programmed using Simpson's rules and a worksheet programmed using the Trapezoid method for comparative analysis.

While the industry standard method for obtaining the area under the lethal rate versus time curve is the trapezoid rule, Simpson's rules are more accurate. However, Simpson's rules are somewhat more difficult to implement.

The package below provides users with the tools to study the differences between F values obtained by both methods and to explore using the time-lethal rate plots the effect of the shape of the curve on the differences between both methods.

The workbook contains two worksheets and the details of the trapezoid sheet are shown in (Figure 4).

* Figure 4 Screen shot of the updated trapezoid spread sheet for calculating F values of thermal processes *

Note these spread sheets use advanced Excel programming knowledge and automatically generate data entry grids. Time entry is not required, this is generated automatically once the delta T value has been added. The user only has to paste the temperature values in.

The spreadsheet has been configured to take up to 1400 time-temperature inputs and can be amended to take thousands if required.

A macro-based control has been added to clear temperature values.

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

* Figure 5 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 for the last few points when working with an even number of data points. The spreadsheet undertakes these calculations automatically.

Note this spreadsheet as with the trapezoid based one, 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.

Again there is an option to calculate the log reduction in the target microbial group following heat processing.

The spreadsheets were updated (May 2018) to correct a 'bug' on the Simpson's worksheet that can result in incorrect coefficients being generated depending on the decimal fraction used in the time interval. Also a macro based control to clear all data and reset the spreadsheets has been added.

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 £70 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.

Note all the cell formulas are locked. The spreadsheet is for your own personal educational use. The Terms and Conditions that apply to the Ice Cream Mix Spreadsheets also apply.

**4) The On Line calculator at at **https://www.dairyscience.info/f-s-t/** is based on the macro-based spreadsheet for determining the lethality (F, value) of a thermal process using the Trapezoid and Simpson's rules that can be downloaded from the link below.** To use you simply paste up to thousands of matched time-temperature values and the Trapezoid and Simpsons macros will calculate F and display the different values of the different calculation methods.

Basic data input validation is also provided.

A screen shot of the spreadsheet is shown in Figure 7 below.

* Figure 7. Screenshot of macrobased calculator for calculating F using the Trapezoid and Simpson's rules.*

Graphs of temperature and lethal rate verus time are plotted and you can choose to select seconds or minutes as the time input units.

A fully working spreadsheet, but limited to five time-temperature inputs can be downloaded for testing. To use this spreadsheet ensure macros are enabled in Excel.

An unrestricted spreadsheet that is not password-protected and offers full access to all code can be downloaded by donating £100.

*Donate £100 to download the Simpson F calculator spreadsheet package.*

**5) F _{0}, B* and C* are important indicators for measuring the effectiveness of UHT processing.**The On Line calculator at https://www.dairyscience.info/uht-t/ is based on the macro-based spreadsheet for determining the lethality (F, value) of a thermal process using the Trapezoid and Simpson's rules that can be downloaded from the link below. To use you simply paste up to thousands of matched time-temperature values and the Trapezoid and Simpsons macros will calculate F0, B* and C* and display the different values obtained using the industry standard method and the more accurate Simpson’s rules.

Basic data input validation is also provided. A screen shot of the spreadsheet is shown in Figure 8 below.

Figure 8. Screenshot of macro-based calculator for calculating F0, B* and C* using the Trapezoid and Simpson's rules.

Graphs of temperature and lethal rate versus time are plotted

An unrestricted spreadsheet that is not password-protected and offers full access to all code can be downloaded by donating £100.

Donate £100 to download the UHT-macro enabled spreadsheet for the determination of F0, B* and C*

**6.) Calculator for determining the lethality (F _{0}, B* values) and chemical changes (C* value, formation of: hydroxymethylfurfural (HMF), lactulose, furosine, and destruction of thiamine) in heated milk integrated using the Trapezoid and Simpson's rules.** The On Line calculator at https://www.dairyscience.info/uht/ programmed using the thermal constants calculated by Claeys

*et al.*(2003) to calculate HMF, lactulose and furosine concentrations following heat treatment in skim, semi fat and full fat milks is based on the macro-based spreadsheet that can be downloaded from the link below. To use you click a control box to load default values, select milk type and simply paste up to thousands of matched time-temperature values and the Trapezoid and Simpsons macros will calculate F0, B* and the chemical changes and display the different values obtained using the industry standard method and the more accurate Simpson’s rules. The spreadsheet is provided with basic data input validation and plots time, seconds, versus temperature and lethality.

_{ref}or E

_{a }or simply accept the values of Claeys

*et al*. (2003) by loading the default values.

*Figure 9. Detail of macro-enabled spreadsheet for calculating lethal values and chemical indicators of UHT processing of milk.*

*Figure 10. Screen shot of macro-based spreadsheet for calculating the lethality and chemical changes of UHT-treatment of milk*.

Users can download a demonstration version, limited to 5 time-temperature values, a full working unrestricted version but with the macros password-protected and a final version that provides full access to all the macro code.

**Download a free demonstration version of the spreadsheet. Click here.**

### Download a full working unrestricted version of the spreadsheet but with the macros password-protected. Use the PayPal link below.

### Download a full working unrestricted version of the spreadsheet and with full access to all the macro code. Use the PayPal link below.

* Donate £150 to download the UHT-macro enabled spreadsheet for the determination of F0, B* and C* and other TTIs. The macros are not password protected and users have access to all the code.*

**7.) The author's Ebook "Thermal processing of acid fruit and vegetable products. Significant microorganisms, recommended processing time / temperatures, and public health significance of spoilage"**

Information on the Ebook and the download link are available at :

https://www.dairyscience.info/index.php/thermal-processing/212-process-values.html

**8) 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.**"

Donate £200 to download *all the spreadsheets above and the author's Ebook*

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