Results 1 to 3 of 3

Thread: Please Help! Excel is beating me up BADLY!

  1. #1

    Please Help! Excel is beating me up BADLY!

    Register for a FREE account, and/
    or Log in to avoid these ads!


    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.

    If anyone could help I would be so grateful.

    Thanks a bunch for reading all this.

    Click image for larger version. 

Name:	FoodList.png 
Views:	96 
Size:	73.2 KB 
ID:	2842Click image for larger version. 

Name:	Lewis1.png 
Views:	92 
Size:	91.8 KB 
ID:	2843

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    It's your lucky day, see attached.

    Basically you need to start with the end in mind. Having a formula tell you how many grams you are allowed is not helpful. Say it tells you that you can have a kg of sugar, but you only want 5 g so now you have a new total.
    The equation you are after is probably sumproduct with a fair dose of index and match thrown in for good measure

    Copy Client for each of your client and name them. Don't forget to refresh the pivot table.
    Info tab is just from your screen grab and a sample only (and OCR not checked). Update this with the correct data and ensure the named ranges (formula's named ranges cover all the data)

    I allow you to specify calories per day per meal. Then calculate how much is unallocated. Then show this in column D. Amount (col E) is per the units (i.e. 100 g). If you want to fix this it's easier doing it once in the info sheet (i.e. change to 1g) but my lunch break is over :-)

    Orange is drop down list
    Red calculations
    Green waiting your input
    Uncoloured also means don't touch :-)
    Attached Files Attached Files

  3. #3
    Thank you so much! I can not tell you how grateful I am. I really appreciate this, sir.

    Thanks once again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts