I am desperately seeking help with a formula. I am trying to count number of columns between non-zero values in a row and reset the count once a non-zero value is present. The problem I am trying to solve is related to perishable inventory. Each lot of inventory will be consumed according to the forecast in a first in, first out process. Once a lot has expired, the forecast should subtract from the next lot until its expiration date. I need to know how much inventory will remain upon the expiration date for each expiration quantity (expected shrink).
Row 2: Daily forecast
Row 3: Expired inventory quantity
Row 4: Expected shrink (Manual formula- this is what I am trying to solve)