Argthtjtr

Let me explain my spreadsheet a little bit. Here is a good screenshot of it:

I have created this spreadsheet essentially because I have gotten tired of the usability issues of just about every nutrition tracking app. So I am creating my own spreadsheet! As you can see going horizontally are different foods/ingredients and below are their nutrition fact values.

Then you will see that towards the bottom there is a row titled "first meal" with some values. This is essentially me saying "2.5 servings of quinoa, 2 servings of lentils" etc. This information is then shown in the table below with "First Meal" in the left.*

My goal now is to allow for two things.

1. Multiple meals


2. Meals made of other meals
   
   
   
   
   • (Imagine there were rows "Carb Breakfast" "Fat Lunch" Protein Dinner" and those were each meal tables but I wanted a table that combined them that was titled "Carb Breakfast, Fat Lunch, Protein Dinner")
   
   
   
   

I am having a bit of difficulty doing so and essentially it comes down to the formula for row D on the "first meal" table. The formula for it is currently SUMPRODUCT(Row Fat, Row First Meal) and as you can imagine SUMPRODUCT(Row Cholestrol, Row First Meal). This works alright for one meal but is just a lot of work to tweak for multiple meals and does not work for multiple meals.

The goal would be that the value of each cell in column D be essentially determined by the following formula.

nutrient = // whatever row we are on

integer sum = 0;

foreach(row in top table):

    integer weight = [ammount of the 'nutrient' in this row]

    integer innersum = 0;

    foreach(meal in meals):

        if (meal is contained in string top left corner of this table):

            innersum += [number of servings of this row];

        sum += innersum * weight;

Forgive me, I am a programmer so that is really the most clear way I could explain this formula. Essentially for each food item look at the top left cell in your table and get the sum of the servings for that food item based on that string then multiply that by how much of the correct nutrient is in the meal.

Ideally this formula for the value in cell D would be implemented sort of as follows:

Take the matrix of all of meals from the top table and filter out rows that have names that are not contained in your top left column. Then simplify this matrix into a single row by adding up all the elements in a column. Then do a sumproduct with that value and the number of the specific nutrient from the meals nutrition facts. The problem is I dont know how to do the whole matrix filter and simplify operation. Frankly I dont know if that is even how spreadsheets work.

*Do not fret I designed this meal with a full day of food in mind.