shadowhound
New Member
- Joined
- Mar 8, 2021
- Messages
- 4
- Office Version
- 365
- 2010
- Platform
- Windows
Hi,
I am building a cooking spreadsheet which calculates the nutritional content of various recipes. My spreadsheet has an ‘Ingredients’ tab and multiple tabs for each recipe. In each recipe tab, I have a list of the relevant ingredients, whose nutritional info I am importing from the ‘Ingredients’ tab using index match and multiplying by the number of servings (Intermediate Step). I then sum up the nutritional data across the ingredients using a sumif. (Final Step)
I am looking for a formula which would combine the intermediate step and the final step. The formula would import the nutritional data for the ingredients, multiply the nutritional data by the number of servings, and then sum up the data, separately for each nutrient.
As an example, I would input the ingredients, the number of servings for each ingredient, and the formula would output the total number of Calories in the recipe. I would then repeat this formula for every nutrient.
Is this possible? I attached a simplified version of the spreadsheet.
Thank you in advance.
I am building a cooking spreadsheet which calculates the nutritional content of various recipes. My spreadsheet has an ‘Ingredients’ tab and multiple tabs for each recipe. In each recipe tab, I have a list of the relevant ingredients, whose nutritional info I am importing from the ‘Ingredients’ tab using index match and multiplying by the number of servings (Intermediate Step). I then sum up the nutritional data across the ingredients using a sumif. (Final Step)
I am looking for a formula which would combine the intermediate step and the final step. The formula would import the nutritional data for the ingredients, multiply the nutritional data by the number of servings, and then sum up the data, separately for each nutrient.
As an example, I would input the ingredients, the number of servings for each ingredient, and the formula would output the total number of Calories in the recipe. I would then repeat this formula for every nutrient.
Is this possible? I attached a simplified version of the spreadsheet.
Thank you in advance.
Cooking Spreadsheet.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | |||
4 | Number of Meals | 3 | ||||||||
5 | ||||||||||
6 | Step 1: Ingredient Entry | |||||||||
7 | Ingredients: | Black Beans | Olive Oil | Onion | White Mushroom | Quinoa | Chicken Broth | Chicken Breast | ||
8 | Unit (g) | 100 | 100 | 100 | 100 | 100 | 240 | 100 | ||
9 | Servings | 4.2 | 0.05 | 4 | 3.5 | 1.35 | 1.5 | 6.8 | ||
10 | Total (g) | 420 | 5 | 400 | 350 | 135 | 360 | 680 | ||
11 | ||||||||||
12 | Step 2: Importing Nutritional Info from Ingredients Tab | |||||||||
13 | Calories | 382.2 | 44.2 | 160.0 | 77.0 | 496.8 | 7.5 | 680.0 | ||
14 | Fat (g) | 1.3 | 5.0 | 0.4 | 1.1 | 8.2 | 0.0 | 17.7 | ||
15 | Saturated Fat (g) | 0.4 | 0.7 | 0.2 | 0.4 | 0.9 | 0.0 | 4.1 | ||
16 | Monounsatured Fat (g) | 0.0 | 3.7 | 0.1 | 0.0 | 0.0 | 0.0 | 4.7 | ||
17 | Polyunsaturated Fat (g) | 0.0 | 0.5 | 0.1 | 0.0 | 0.0 | 0.0 | 2.9 | ||
18 | Cholesterol (mg) | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 496.4 | ||
19 | Total Carbohydrate (g) | 71.4 | 0.0 | 37.2 | 11.6 | 86.4 | 0.0 | 0.0 | ||
20 | ||||||||||
21 | Step 3: Sum of the Ingredients | |||||||||
22 | Unit | |||||||||
23 | Calories | 615.9 | ||||||||
24 | Fat (g) | g | 11.2 | |||||||
25 | Saturated Fat (g) | g | 2.2 | |||||||
26 | Monounsatured Fat (g) | g | 2.8 | |||||||
27 | Polyunsaturated Fat (g) | g | 1.2 | |||||||
28 | Cholesterol (mg) | mg | 165.5 | |||||||
29 | Total Carbohydrate (g) | g | 68.9 | |||||||
Quinoa-BlackBeans |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C8:I8 | C8 | =INDEX(Ingredients!$B$4:$ZW$12,MATCH('Quinoa-BlackBeans'!$B8,Ingredients!$B$4:$B$12,0),MATCH('Quinoa-BlackBeans'!C$7,Ingredients!$B$4:$ZX$4,0)) |
C10:I10 | C10 | =+C8*C9 |
C13:H19 | C13 | =INDEX(Ingredients!$B$6:$H$12,MATCH('Quinoa-BlackBeans'!$B13,Ingredients!$B$6:$B$12,0),MATCH('Quinoa-BlackBeans'!C$7,Ingredients!$B$4:$H$4,0))*C$9 |
I13:I19 | I13 | =INDEX(Ingredients!$B$6:$W$12,MATCH('Quinoa-BlackBeans'!$B13,Ingredients!$B$6:$B$12,0),MATCH('Quinoa-BlackBeans'!I$7,Ingredients!$B$4:$X$4,0))*I$9 |
D23:D29 | D23 | =SUM(OFFSET($C$12,MATCH($B23,$B$13:$B$19,0),0):OFFSET($Z$12,MATCH($B23,$B$13:$B$19,0),0))/$C$4 |
Cooking Spreadsheet.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | |||
4 | Black Beans | Quinoa | White Mushroom | Olive Oil | Onion | Chicken Broth | Chicken Breast | |||
5 | Unit (g) | 100 | 100 | 100 | 100 | 100 | 240 | 100 | ||
6 | Calories | 91.0 | 368.0 | 22 | 884 | 40 | 5 | 100 | ||
7 | Fat (g) | 0.3 | 6.1 | 0.3 | 100 | 0.1 | 0 | 2.6 | ||
8 | Saturated Fat (g) | 0.1 | 0.7 | 0.1 | 14 | 0.0 | 0.0 | 0.6 | ||
9 | Monounsatured Fat (g) | 0 | 0 | 0 | 73 | 0.0 | 0.0 | 0.7 | ||
10 | Polyunsaturated Fat (g) | 0 | 0 | 0 | 10.5 | 0.0 | 0.0 | 0.4 | ||
11 | Cholesterol (mg) | 0 | 0 | 0 | 0 | 0 | 0 | 73 | ||
12 | Total Carbohydrate (g) | 17.0 | 64 | 3.3 | 0 | 9.3 | 0 | 0 | ||
Ingredients |