Native FIFO Inventory Costing Function for Excel (Proof of Concept with LAMBDA)

The LAMBDA formula itself is fully self-contained and demonstrates that FIFO costing can be implemented with native Excel functions, even if it requires multiple steps and careful state management. Seeing the formula in action, whether in a file or on paper, helps illustrate the complexity involved in replicating standard accounting logic in Excel.

On the comparison between recursion and a SCAN-based approach: both methods are valid and highlight different ways modern Excel can handle dynamic calculations. Using SCAN over a sorted and stacked array can make the logic more transparent and easier to follow in a spreadsheet, while recursion can closely mirror how FIFO engines work in ERP systems by explicitly tracking remaining inventory. Both approaches offer valuable insights into what is possible with Excel today😲.

I also appreciate the idea of extending this further — for example, embedding FIFO logic in GROUPBY to handle multiple products or regions. It shows the potential of modern Excel functions beyond single-item calculations. Overall, these examples reinforce the same point: Excel is flexible and powerful, but a native FIFO / LIFO / weighted-average inventory function would make these calculations simpler, more accessible, and easier to audit for a wider range of users.

Overall, I think the different approaches discussed here — recursive formulas, SCAN-based solutions, and potential extensions for multiple products or regions — reinforce the same point: Excel is powerful, but a native FIFO / LIFO / weighted-average inventory function would make these calculations more accessible, auditable, and easier to maintain for a wide range of users.