Results 1 to 6 of 6

Thread: Food list - Formula to calculate nutritional macros

  1. #1

    Food list - Formula to calculate nutritional macros



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

    Hi there,

    I wonder if this can be done,

    For my nutritional plan i have a section for each meal I'm going to have, eg. meal 1, meal 2, meal 3
    And i have a section where i have inputted certain protein sources, carbohydrate sources with nutritional information per 100 g/ml.

    Is there a way via data validation to allow a list of food groups to be selected from a list and then the amount in g/ml to be entered in like an input box, then via reference to the info from the sources section, having it populate the nutritional information.

    For eg. if i have chicken that has 20g of protein per 100g and for meal 1 i select -> chicken and i input 200g .. the column under "protein" will show "40g" ..

    i have attached an excel doc of the 2 sections to show you what i mean.. is this possible to do?

    Thanks very much
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    For the Data Validation, create a list of all the sources available and make it a Named Range. I put it in column T in the attached.

    You can make the list Dynamic (DNR), so that you can add and remove sources at will.

    Go to Formulas tab, then Define Name from the Defined Names section, then enter a name like: Sources and apply formula: =OFFSET(Sheet1!$T$1,1,0,COUNTA(Sheet1!$T:$T)-1,1) click Ok to accept

    Now select G3:G6 and go to Data|Validation and select List from the Allow menu, then apply formula: =Sources

    Finally, in L3 apply formula:

    =IFERROR(INDEX(B:B,MATCH($G3,$A:$A,0))*$J3/100,0)


    copied across and down the table.

    Now make selections, enter amounts and see the results.
    Attached Files Attached Files


  3. #3
    That is amazing! thank you very much!!!

    one minor querie, if i want to make heading in the list, i have tried to, but is there a way to centralize them in the select list?
    to also have a text in the amount column that says "enter amount" then when u click on it, it goes blank for you to input.

    Iv attached the file
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    I am not quite sure what you are after here. Why do you want the headings in the drop down?


  5. #5
    Its not like a compulsory thing, but just so things are in order. but you have helped me a TON!!! thank you so very much

    Ill definitely reccomend this forum to any of my friends!

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    Ok Great


Posting Permissions

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