FIFO Inventory Formula Challenge

Twifoo 

Robert

I appreciated your description of the problem and your analysis of solution requirements.  I also learn from the detailed techniques that have been deployed to make a formula more concise, though that has never been an objective of mine.  My shortcoming is that I find 'concise' notations, such a regular expressions (RegEx), fiercely difficult to read.

I wish I could think up competition rules for 'the clearest top-down solution strategy' in which it is the number of overall operations that count.  In my solution, posted on 13 Feb, I used Charles Williams FastExcel functions to solve the problem as an accumulate/minimise/difference sequence.  That itself is simply a solution strategy to the generic problem of 'allocate an amount to consecutive bands' (e.g. the allocation of taxable income across tax bands).  Your challenge is the same problem and could also be solved without placing limits on the purchasing policy that ensure only the final two tranches are present within the residual stock.  Having got an ACCUMULATE function, I now need an ALLOCATE function.

Once the LAMBDA functions are released, one could imagine a challenge of:

Solve Twifoo 's year2 problem for all 5 products within a single recursion formula.

To return to the topic, I think the ingenuity required to create the problem is even more impressive than its solution; congratulations.