Accepting input from multiple rows in formula

1

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.

image
image619×680 34.1 KB

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!

2

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

3

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?