• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculation field

K

kirk

New Member
HI Guys


First post here.


Im currently setting up a excel document for a diet plan. The idea is to add the macronutrients of the foods i eat to determine the calories i consume.


I have 2 sheets, 1 is the diet plan as such, and 1 is the source data. In teh source data, i have listed all the foods that i eat with its serve, cals, protein etc.


The main sheet, or the diet plan sheet, i have a drop down menu from the source sheet where i can select the particular food item. Ive then added a v lookup which also bring in field from the source data such as calories.


This calories field is then totalled to get the total.


Why question relates to a field where i want to calculate the serves. Eg, I would type in 100g in the field, and this would bring up 100g of the chicken. The chicken item on the source data would be setup as 1g, so effectively the calcuated field would mutliply the 1g by 100 given thats my entry.


Is their a way i can do this? If i type in 100 into a field, is their a way it could calcuate the items that are brought up via the vlookup instaneously?


I dont necessary want a formula, but just a entry field which performs this calculation in the background.


Any help is greatly appreciated.


Thanks
 
Kirk

Welcome to the Chandoo.org forums


You will need to do 2 things


1. Lookup the chicken from your source data and retrieve the corresponding calories per gram etc

2. Multiply that by your serve size


So you will end up using a formula along the lines of

=Serve Size * Index(Source Data, Match(Chicken, Food Column, 0), Food Column No)


Serve Size is the cell which has your serve size

Source Data is the range of your source data

Chicken is the cell with your food listed in it

Food Column is the Range in the Source Data Tavble where the food is listed

Food Column No is the column No in the Source Data which contains the food Calories per gram


If you want to share your file I can give more specific help
 
Thanks Hui, appreciate your help.


It would make more sense if i gave your a copy of my current workbook.


Please note Sheet 2 is currently where i want all the information from the source data to be input and calculated.


Link is http://hotfile.com/dl/117516040/3ee08be/Diet_Plan.xlsx.html


If you could use the eggs white from the drop down menu as your example, and maybe make that 6 serves rather than the serve of 1 which i have in the source data, may be easier for me to visualise the formula.


Thanks
 
Change the named range

Food: =OFFSET('Source Data'!$A$2,,,COUNTA('Source Data'!$A$2:$A$1000),COUNTA('Source Data'!$2:$2))


Change

Sheet2!F9 to =$E9*OFFSET('Source Data'!$A$1,MATCH(Sheet2!$D9,INDEX(Food,,1),0),COLUMN()-3)

Copy across and down
 
Change the named range

Food: =OFFSET('Source Data'!$A$2,,,COUNTA('Source Data'!$A$2:$A$1000),COUNTA('Source Data'!$2:$2))


Change

Sheet2!F9 to =$E9*OFFSET('Source Data'!$A$1,MATCH(Sheet2!$D9,INDEX(Food,,1),0),COLUMN()-3)

Copy across and down
 
Thanks Hui


So i got rid off the vlookup and replaced it with: $E9*OFFSET('Source Data'!$A$1,MATCH(Sheet2!$D9,INDEX(Food,,1),0),COLUMN()-3)


Im still able to use the 'serves' field even though i havent input the first equation(as im still struggling to change the named range), is it critical have the first formula?


Could you also explains what each formula means i.e reading it, what shoudl i be thinking?


Thanks
 
You must log in or register to reply here.
Back
Top