Forum Discussion
Re: FIFO Inventory Formula Challenge
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.
27 Replies
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)I simply adopted the adjective "traditional" mentioned by PeterBartholomew1 .
Perhaps, "traditional functions" would refer to those which are not available to non-Microsoft 365 users. Using LET is analogous to "legal cheating" for allowing the definition of names outside the Name Manager. For the purpose of my formula challenge, I will allow such, not because I allow cheating, but because it is legal.Twifoowrote Using LET is analogous to "legal cheating" for allowing the definition of names outside the Name Manager. For the purpose of my formula challenge, I will allow such, not because I allow cheating, but because it is legal.
That's one way of looking at LET.
I was thinking of LET more as a way of using what we often refer to as "helper columns," just that all of those "helper columns" end up being incorporated into (or referenced within) a single formula. In fact, that has given me insight into how to use LET more extensively, whenever tempted toward the "helper column" approach.
- My analysis of the LET syntax reveals that it defines a name for the value in an argument, then use such name in the main formula. If such value, which has now been defined as a name, won't be reused in the main formula, such definition becomes superfluous!
Thank you for the explanation. Still had no time to check in details, will do.
Question to your latest post, related to this case what do you mean under traditional functions? If people have LET() they shall have SORT(), FILTER(), etc. If only LAMBDA() is still exotic, but here it is only cosmetic on the top of LET().
