How to Calculate Nutrition Facts with Excel or Open Office Calc

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

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

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

The quantitative order simply means that the largest constituent ingredient should be listed first, then the second largest, 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 italic text

There are 14 allergens that must be indicated on the labeling if they are present in the ingredients of the product. These include wheat or oats or any other gluten-containing cereal and also include milk, eggs, fish, crustaceans, mollusks, to name a few.

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

Historically, text could be incredibly difficult to read as manufacturers stuffed 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 that all text be legible with a specific height of the letter “x” in the font not less than 1.2 millimeters. In layman’s terms, that means the standard Arial or Times New Roman font should be 6.5 point and size.

The second phase of the regulations that will take effect in December requires that nutrition data be provided with all prepackaged foods so that consumers can make decisions regarding the nutrition of the foods they buy.

The law stipulates that this information must be transmitted to the customer for every 100 grams.

It is also possible to transmit the information further per serving, so that, 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 nutritional values ​​in a serving, for example a cookie or a small piece of chocolate. But the food producer must also provide the information in a per 100g format in all cases.

How to Calculate Nutrition Facts

To calculate the nutritional values ​​of prepackaged foods for sale to the public, food production companies must know the nutritional values ​​of the constituent ingredients of their product. Perhaps the best way to demonstrate how to calculate nutrition facts is to give an example; a ham and mustard sandwich.

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

Food manufacturers should start with the basic nutrition facts for each ingredient; As mentioned, the legislation requires that nutritional data be provided for every 100 grams. Since all manufacturers must do this, most food companies should be able to get that information directly from the packaging of the products they buy or by talking to their supplier.

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

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

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

Once the table of data per 100 grams is prepared 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 use (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 how many calories, how much fat, saturated fat, etc. is present in the recipe. The calculation will consist of dividing the nutrition data by 100g by 100 and then multiplying that by the weight of what constitutes the ingredient.

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

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

And this is how nutritional data is calculated with Microsoft Excel or Open Office Calc.

At the moment, food manufacturers in the UK are faced with a great challenge in achieving the goals set out in the regulations and must address them very quickly if they have not already done so.