Help with some averages

Ok, so going to keep this as simple as possible. I have a fish tank that requires me to put in certain elements like Zinc and Nickel. Each month, I send in a water test and once I get back the results, I do a correction dose or doses of how much of each element that needs to be added for the month.

I log all of these amounts in a corrections table.

I log the elements dosed in an elements table.

In the elements table, I roll up each correction’s amounts. So, if I dosed 1ml on Oct 1, 1ml on Nov 1, and 1ml on Dec 1, in the elements table the rollup will say 3.

Now, in the elements table I ask Airtable via a forumla to average the doses I’ve put in and spit out a prediction of how much I need to dose each week. In the example above, my formula would spit out 1ml average, divide that by 4, I get 0.25ml each week.

This works fine, now I’m able to each week calculate a guess of how much to dose, and instead of sending in a water test each month, I can do it every other month or once per quarter.

Now, here’s the tricky part. I want Airtable to continue to average the doses, but since I only ask it for 90 days of corrections, as the months progress, if I don’t do a dose logged as a “Correction” it wll eventually fall to 0, and my 1, will go to .66, then .33 and then to 0. So, what I am trying to figure out a way, is to include the 0.25ml each week that I dose as part of the calculation as the dates progress with out having to manually add a record to the corrections table each week.

Let me know if anyone has thoughts.