I am tracking daily capacity at a production facility where capacity cannot exceed 20. If capacity is > 20, the remainder would carry over to the next day. If the next day's capacity is > 20, the remainder would carry forward until the capacity on a given day is <= 20.
I'm able to calculate the remainder and add that to the next days total using lookup but can't figure out how to make this work when moving values forward that make capacity > 20 for days in a row. I think I can get there by evaluating for day 1 first, then writing a set of subsequent calculations for each day following looking at what the calculations before came up with but hoping for a more elegant solution.
I've attached a packaged workbook for example. Expected output is:
Side note: I deal with time series and moving values around frequently - if there are known resources that deal with advanced time series topics like this or similar I would greatly appreciate being pointed in their direction!