Forum Discussion

Twifoo's avatar
Twifoo
Silver Contributor
Feb 08, 2021

FIFO Inventory Formula Challenge

To revel my second anniversary as a member of this EXCELlent Community, I hereby proffer this challenge to season your array manipulation skills. Perhaps, keenadvice mathetes Riny_van_Eekelen PeterBa...
Formulas and Functions
Twifoo's avatar
Twifoo
Silver Contributor
Feb 10, 2021
I'm already accustomed to your inherent aversion of using direct cell references. Even so, I'm impressed at your improvement on the solution of SergeiBaklan .
As I had previously advocated, traditional formulas are accessible to more users than modern Excel functions. For that reason, my formula challenges reach the widest possible audience, rather than a privileged few.
tboulden's avatar
tboulden
Iron Contributor
Feb 10, 2021

TwifooDeleted previous reply, getting used to the set-up on the forum. I've used LET below for readability and in the first cell in the attached, but left other formulas using older functions. On the right track?

 

=LET(
Product,"Courage",
ProdFilter,(ProductName=Product),
PurchFilter,(TrnType="Purchase"),
InventoryOnHand,SUM(CHOOSE(MATCH(TrnType,{"Purchase";"Sale"},0),1,-1)*ProdFilter*TrnQty),
LastPurchasePrice,INDEX(TrnPrice,MAX(TrnNum*PurchFilter*ProdFilter)),
LastPurchaseQty,INDEX(TrnQty,MAX(TrnNum*PurchFilter*ProdFilter)),
LastPurchaseInv,MIN(InventoryOnHand,LastPurchaseQty),
PrevPurchasePrice,INDEX(TrnPrice,LARGE(TrnNum*PurchFilter*ProdFilter,2)),
PrevPurchInv,(InventoryOnHand-LastPurchaseInv),
LastPurchaseInv*LastPurchasePrice+PrevPurchInv*PrevPurchasePrice)

Resources