How to Calculate Nutrition Data Using Excel or Open Office Calc


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

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

The new regulations simply require that allergens 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 largest constituent ingredient should be listed first, then the second largest, etc. The percentages of these ingredients should also be included.

There are several ways to highlight the ingredients; Users can use bold text to underline text in color or italics

There are 14 allergens which must be indicated on the labeling if they are present in the ingredients of the product. 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 readability of text on food labels.

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

The new regulations require all text to be readable with a specific height of the letter "x" in the font not exceeding 1.2 millimeters. Simply put, this means that the standard Arial or Times New Roman font should be 6.5 point and size.

The second phase of the regulations that will come into effect in December requires that nutritional data be provided with all prepackaged foods so that consumers can make choices about the nutrition of the foods they purchase.

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

It is also possible to transmit additional information per portion. So, for example, a sandwich would constitute a serving so that a food producer could provide the information based on the entire sandwich. The food producer can also indicate the nutritional values ​​of a serving, for example, a cookie or a small piece of chocolate. But the food producer must also provide the information in the format per 100 g in all cases.

How to calculate nutritional data

In order to calculate the nutritional values ​​of prepackaged foods intended for sale to public food production companies, companies must 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 be made up of four ingredients; we will have the bread, the ham, the mustard and the margarine or the butter to make a sandwich. Each of these ingredients will be incorporated according to the lines of a recipe; That is, there will be a specific weight of each product to constitute a standard product.

Food manufacturers should start with the basic nutritional data for each of the ingredients – as mentioned, legislation requires that nutritional data be provided per 100 grams. As all manufacturers are required to do, most food production companies should be able to obtain this information directly from the packaging of the products they purchase or by chatting with them. provider.

In our example, the food producer could group the data of the constituent ingredients in a table. The information that needs to be conveyed includes energy in kilojoules and kilocalories; they also need to carry total fat, saturated fat, carbohydrate, sugar, protein, and salt – all in grams.

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

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

Once the data table is prepared for 100 grams for all ingredients, the food producer should 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 (eg 30 grams), 10 grams of mustard 5 grams of margarine.

Once this has been done, a simple calculation is applied to each of the constituent ingredients to determine how many calories, how much fat, saturated fat, etc. are present in the recipe. The calculation will be to divide the nutritional data by 100g by 100 and then multiply that by the weight of that constituent in the ingredient.

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

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

And that's how calculating nutritional data using Microsoft Excel or Open Office Calc.

Right now UK food manufacturers face a huge challenge in meeting the targets set in the regulations and they need to tackle them very quickly if they don't. already done.



Please enter your comment!
Please enter your name here