How to Calculate Nutrition Data Using Excel or Open Office Calc

EU Directive 1169/2011 comes into full force on December 13, 2016. The first phase of this directive came into force in 2014 on December 13, but the second phase requires nutritional data, which raises the question of how to calculate the data. nutritional.

The first phase of this regulation required all ingredients on labels to include allergen information in the ingredient list. Prior to this regulation, it was legally acceptable to include allergen information in a separate area of ​​your label.

The new regulations simply require allergens to be highlighted in the product’s unique ingredient list, but the ingredients must also be listed in quantitative order.

Quantitative order simply means that the most important constituent ingredient should be listed first, then the second and so on. The percentages of these ingredients should also be included.

There are several ways to highlight ingredients; Users can use bold text, underlined text, colored text, or italicized text.

There are 14 allergens that must be listed on the label if present in the product ingredients. These include wheat or oats or any other cereal containing gluten and also include milk, eggs, fish, shellfish, shellfish to name a few.

Another aspect of the legislation was to harmonize the legibility of text on food labels.

Historically, text could be incredibly difficult to read because manufacturers crammed as much information into as small a section of the label as possible to maximize the marketing potential of the rest of the label.

The new regulations require all text to be legible with a specific height of the letter “x” in the font of at least 1.2 millimeters. In simple terms, this means that the standard Arial or Times New Roman font should be 6.5 point size and point size.

The second phase of the regulations that will come into force in December requires that nutrition information be provided with all pre-packaged foods so that consumers can make choices about the nutrition in the foods they buy.

The law states that this information must be communicated to the customer per 100 grams.

It is also possible to pass the additional information per serving, for example, one sandwich would constitute one serving, so a food producer could provide the information based on the whole sandwich. The food producer can also indicate the nutritional values ​​of a portion, for example a biscuit or a small piece of chocolate. But the food producer must also provide the information in the format per 100 g in any case.

How to calculate nutritional data

In order to calculate the nutritional values ​​of pre-packaged foods intended for sale to the public, food production companies need to know the nutritional values ​​of the constituent ingredients of their product. Perhaps the best way to show how to calculate nutritional data is to give an example; a ham and mustard sandwich.

A ham and mustard sandwich can consist of four ingredients; we will have bread, ham, mustard and margarine or butter to make a sandwich. Each of these ingredients will be incorporated along the lines of a recipe; that is, there will be a specific weight of each product to constitute a standard product.

Food manufacturers must start with the basic nutritional data for each of the ingredients – as mentioned, the legislation requires that the nutritional data be provided per 100 grams. As all manufacturers are required to do, most food producing companies should be able to get this information directly from the packaging of the products they buy or by talking to their supplier.

In our example, the food producer could compile the constituent ingredient data into a table. The information to be transmitted includes energy in kilojoules and kilocalories; they must also convey total fat, saturated fat, carbohydrates, sugar, protein and salt – all in grams.

Food producers can also list monounsaturated fats, polyunsaturated fats, polyols and starch (which are carbohydrates) and fiber if they wish.

The order of nutrients is specific and must be respected to comply with the regulations.

Once the data table is prepared for 100 grams for all ingredients, the food producer must understand the weight of each product used in the recipe to make the sandwich. In this example, the food producer would need to know the weight of two slices of bread (say 60 grams), the ham they are using (say 30 grams), 10 grams of mustard, 5 grams of margarine.

Once this is done, a simple calculation is applied to each of the constituent ingredients to determine the number of calories, the amount of fat, saturated fat, etc. present in the recipe. The calculation will be to divide the nutritional data per 100g by 100, then multiply by the weight of that constituent in the ingredient.

For example, if 100g of ham is 350 calories, divided by 100 gives 3.5 calories per gram. 3.5 calories per gram x 30 grams used in the recipe equals 105 calories.

Once this is complete, the food manufacturer will have an accurate indication of the total nutrient data for the ham and mustard sandwich by simply adding the values ​​for each constituent ingredient as a total for the recipe.

And that’s how to calculate nutritional data using Microsoft Excel or Open Office Calc.

Currently, UK food manufacturers face a huge challenge in meeting the targets set out in the regulations and they need to address them very quickly if they haven’t already.

About the author


Leave a Comment