Creating macro for nested calculations within calculations

[LizN]

Hi everyone,

I was hoping to get advice on creating a macro for calculating values which are then used in subsequent calculations (which are then often used in subsequent calculations).
The project I'm working on is using a food composition database, which has a list of 5700 odd foods. I am trying to assign values to each food based on their composition (called X values for this post). Some of these foods are "recipes", whereby there is a list of the ingredients in each food (for example the food item beef cooked in oil has the ingredients 'beef' and 'oil' and their proportions in the overall food). To calculate the X values for recipe foods, I need to calculate the X value of all of the individual ingredients, multiply them by the proportion they are present in the original food, then add them together. I've been doing this manually with the help of some VLOOKUPs and pivot tables, and overall it has been working ok, though very time-consuming.

The problem arises because some of these ingredients within each food have recipes themselves, so in the 'beef, cooked in oil' example, the oil itself is recipe of multiple different types of oil. So basically I need to calculate the X value for the oil, then paste it to the spreadsheet, then calculate the X value of the 'beef, cooked in oil'. Some recipes have multiple levels of recipes within them, up to 5 or 6 levels of recipe. I was doing all these manually and getting through them, but have realised I made an error with calculating the X value for a food which is an ingredient in many recipes (which themselves are ingredients within recipes etc), so I'm hoping there might be some way to speed up this process.

I've attached screenshots of an example workbook to show what I'm meaning (sorry, my work laptop which I'm currently on doesn't allow me to download xl2bb, but should be able to do so later tonight if anyone would like have the workbook). In the sheet 'main food list', column A is the survey ID, which is the unique ID for each food, which I use to link sheets etc, and column C is the current X value (though this needs to change for some foods if the ingredient X value changes). This sheet normally has over 5700 foods, all of which will have X values. The second sheet 'recipe list' is the list of ingredients for foods in the 'main food list' which have multiple ingredients (the ingredients themselves are normally all items within the 'main food list'). In this sheet, column A is the ID of the main food, column D is the ID of the ingredient within the main food, column C is the X value of the ingredient, column G is the proportion of the ingredient within the main food (e.g. the food 18101022 'Beef, blade steak, untrimmed, baked, roasted, fried, grilled or BBQ'd, fat not further defined' is 98% beef and 2% oil), and column H is the X value multiplied by the proportion. Using pivot tables (not included in this example) I then sum the X values for each food in column H to get the overall X value for the food. When recipes are ingredients within another recipe, I have been manually pasting the calculated X value into the 'recipe list', for example the orange value for ingredient ID 14602003 is the sum of the X values for the rows 2 - 19.
It gets really tricky if I need to change any of these X values. In the example spreadsheet ('recipe list'), if I need to update the X value listed in column C for 14401010 (row 13), then I need to manually recalculate and replace all of the X values for the foods they are ingredients in, and then the foods they are ingredients in etc.

Sorry I know this is hugely confusing, but I was wondering if anyone has any tips on writing a macro to cover this, or even if it is do-able at all? I'm fairly proficient with Excel formulas and pivot tables, but am a complete novice with macros.

Thank you very much,
Liz

 

[LizN]

Main food list sheet:

MrExcel sheet example.xlsx
	A	B	C
1	Survey ID	Food Name	Final X value per gram
2	18101022	Beef, blade steak, untrimmed, baked, roasted, fried, grilled or BBQ'd, fat not further defined	0.01640567
Main food list

 

[LizN]

'Recipe list' sheet

MrExcel sheet example.xlsx
	A	B	C	D	E	F	G	H
1	Food Survey ID	Food Name	Ingredient X value per gram	Ingredient Survey ID	Does the ingredient have a recipe?	Ingredient Name	Weight as % of recipe total	X value (calculated as proportion of product)
2	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00326	14402007	NO	Oil, olive	0.587959127	0.001917
3	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00116	14402003	NO	Oil, canola	0.126484397	0.000147
4	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00135	14401009	NO	Oil, sunflower	0.03369235	4.55E-05
5	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00189	14402008	NO	Oil, peanut	0.020436344	3.86E-05
6	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.002103	14402009	NO	Oil, rice bran	0.020436344	4.3E-05
7	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.001447	14401003	NO	Oil, grapeseed	0.003590168	5.19E-06
8	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.002103	14402005	NO	Oil, macadamia	0.001380834	2.9E-06
9	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.001447	14401005	NO	Oil, maize	0.002209334	3.2E-06
10	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00231	14401007	NO	Oil, sesame	0.008285004	1.91E-05
11	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.001447	14401004	NO	Oil, linseed or flaxseed	0.000276167	4E-07
12	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00068	14401008	NO	Oil, soybean	0.001380834	9.39E-07
13	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.001775	14401010	YES	Oil, vegetable	0.106876553	0.00019
14	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00587	14502002	NO	Oil, copha	0.000552334	3.24E-06
15	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.004394	14503001	NO	Fat, solid, blend of animal & vegetable oils	0.000552334	2.43E-06
16	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00837	14101004	YES	Butter, not further defined	0.056614195	0.000474
17	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.005961	14201001	NO	Dairy blend, butter & edible oil spread (~80% fat), sodium 485 mg/100 g	0.002485501	1.48E-05
18	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00837	14102001	NO	Ghee, clarified butter	0.00690417	5.78E-05
19	14602003	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.00113	14307005	YES	Margarine spread, monounsaturated or polyunsaturated, regular fat (>50% fat), not further defined	0.01988401	2.25E-05
20	18101022	Beef, blade steak, untrimmed, baked, roasted, fried, grilled or BBQ'd, fat not further defined	0.01668	18101020	YES	Beef, blade steak, untrimmed, raw	0.98	0.016346
21	18101022	Beef, blade steak, untrimmed, baked, roasted, fried, grilled or BBQ'd, fat not further defined	0.002987	14602003	YES	Fat or oil, not further defined, for use in home cooked meat, fish, poultry & mixed dish recipes	0.02	5.97E-05
Recipe list