Please Help! Excel is beating me up BADLY!

[Dale7717]

Hello,

I'd like to start off by stating that I am by no means an expert with Excel. I know how to use it, but as for creating an intermediate to advanced file I am awful.

I am a nutritionist who creates healthy meal plans for people & I created a simple Excel document to assist me.

It's borderline adequate, but I still have to do a lot of manual calculations & really, I'm sure I can save a whole bunch of time if I had a more comprehensive Excel file to use.

My work requires me to base meals upon specific amounts of calories. Of course, these calories will be comprised of different macro-nutrients (macros) from food. These are then calculated into the correct portion sizes (in grams) to equate to the required calorific intake.

The three macro-nutrients are:

1. Carbohydrates
2. Proteins
3. Fats

For every 1 gram the calorie values would be:

1. Carbohydrate = 4 calories
2. Protein = 4 calories
3. Fat = 9 calories

So for an example:

100g of Avocado = 20g of fat (x9 = 180 calories), 2g of Protein (x4 = 8 calories) and 2g of carbohydrates (x4 = 8 calories).

This means that 100g worth of Avocado would have a total of 196 calories between it's macro values.

When I create a meal plan, I need to ensure that the meals contain the correct amount of carbs, proteins & fats to equal a specific total calorie intake.

So let's say I needed 1000 calories in total.

250 calories from carbs = 62.5g (divided by 4)
500 calories from proteins = 125g (divided by 4)
250 calories from fats = 27g (divided by 9)

I would have to determine what foods and the weight of those foods would equate to those numbers.

I have a list of foods and their macro-nutrient values per 100g which I use to create the meals (please see the attached file).

I colour coded them so GREEN are foods denser in fat, BLUE are denser in carbs & RED are denser in protein.

From that it's basically me mixing and matching suitable food combinations with a calculator and getting as close to the required total calorific intake as possible. Keeping in mind I have to also ensure that the overall calorie intake is comprised of the specific calories required for each macro-nutrient.

Ideally, I would like to type in the calories I require and the spreadsheet to tell me how many grams of food I need.

So if I typed in "250" calories for fat, it would look at my food list values and calculate how many grams I would need for each food in the GREEN or fat dense column. Of course, because each food has a little bit of one or both of the other macro-nutrients, I would also need it to update those as well for each food.

How can I do this?

The only thing I could think of would be to create a formula that works out what 1g is, multiplies it by the macro calorie value (carb =4, protein = 4, fat = 9) and then multiplies that by the figure I put in which will always be a variable.

So if I type in 250 for fat, it will look at avocado, break it down into what the value of 1g would contain, and then calculates the macro calories by multiplying their specific values by 250.

I've included a picture of one of my meal plans as well. Hopefully that may give a better reference to what I'm talking about.

I hope I haven't been too confusing & I'm really sorry this post is so long. I think the formula and application would be pretty simple for somebody who knows how to create a spreadsheet, but I'm afraid I'm ignorant regarding this.

Thank you

 

[RatExcel]

Hey,

I'm working on this one Will let you know soon

 

[Dale7717]

Thank you so much!

 

[RatExcel]

Ok, here you go. That's not perfect but works pretty well I think.

In column A you have all product names just like on your screen.
In column B there is amount of grams of the product. It has to be in grams! For example on your screen you have Baked Beans In Tomato Sauce and its amount is in number of cans (per 1/2 can). It cannot be like that so I assumed that 1/2 of a can = 100 grams. You can correct it.
In columns C to E there is amount of Fats, Proteins and Carb that are in X grams of certain product.

In columns S to U there are amounts of calories per 1g for Fats, Proteins and Carbs - they are used in calculations. Below are the amounts of Fats, Proteins and Carbs in kCal that you think should be delivered in the meal. You can change them as you wish.

In column G there are dropdown list so you can select up to 4 products for each category.
In columns H to L there are some helper columns that I used to do my calculations, you don't have to worry about them.
In columns M there is amount of grams of specific product that you should prepare to deliver the specified amount of kCal for all micro elements.
In columns N to P there are the amounts o kCal for each micro element that will be delivered with the specific product amount in grams that is in column M.
On row 14 in columns M to P you have the sum of grams and kCal for Fats, Proteins and Carbs.

Workbook:

https://app.box.com/s/i57uo8in6uhxgw6wwihp

 

[Dale7717]

Man, you guys are the best! I can't even begin to thank you all enough! Thank you so much! Both of these will be so very helpful to me! Thanks once again!