Accepting input from multiple rows in formula

I am starting a new diet that is based on weights (grams) of food rather than calories. There are multiple food groups. I can choose however many foods from each food group as long as I don’t go over the weekly gram allowance… it’s a little complicated.

Anyway, I am trying to build a tool to help with the calculating/tracking so I don’t have to think about it as much. I have a table for each food group with the food name and the weekly allotted grams.

The image below is one of the food groups “first course” - so to be clear I cannot have ALL of those, I can have up to the amount in “weekly allotment” of any of them.

So if I have 100 grams of Couscous one day, I need ALL the values in the “remaining” column for that table to be reduced by the percentage consumed (in this case 20.83%). This is where I am getting stuck.

I am able to reduce the values in the row. I did this by adding a column to calculate the percentage (thisRow.Consumed/thisRow.[Weekly Allotment]) *100 and then having the “remaining” column reduced by that percentage (thisRow.[Weekly Allotment] * (100 - thisRow.[Percentage Con]) /100 )

But I need the “remaining total” in ALL rows to be reduced by the percentage/s in any row in the table. Any help is greatly appreciated!

Figured it out. (thisRow.[Weekly Allotment] * (100 - Sum(thisTable.[Percentage Con]) ) /100 )

Hi @anon57110140 and welcome to the community! Thank you sharing the doc straight off.

Allow me to rephrase what I heard and please lmk if I misunderstood:

  1. Sum up all the %'s consumed across all rows → Call this Total % Consumed
  2. Remaining value for a row = Weekly Allotment - (Weekly Allotment * Total % Consumed)

Is that correct?

If so you’ll need to make 2 changes to your doc:

  1. The Percent Con column formula should change to this:
    image

  2. The remaining column formula would be this:
    image

Here’s a page in this doc with the formulas corrected to the above.

Does this solve the issue for you?

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.