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...

FIFO Inventory Formula Challenge.xlsx14 KB

Formulas and Functions

lori_m

Iron Contributor

Feb 11, 2021

Twifoo

One more... was trying to save characters, could be made clearer by using longer names:

=LET(
T,TrnType="Purchase",
P,ProductName=J18,
B,SORTBY(IF({1,0},TrnQty*-P*-1^T,TrnPrice),TrnType,1,TrnNum*P,-1),
S,SUM(INDEX(B,,1)),
MMULT(MIN(S,INDEX(B,1,1))*{1,-1}+{0,1}*S,INDEX(B,{1;2},2))
)

and a slightly shorter one based on the sample file:

=LET(
T,TrnType="Purchase",
P,ProductName=J18,
S,SUM(-1^T*-P*TrnQty),
MMULT(MIN(S,LOOKUP(2,1/T/P,TrnQty))*{1,-1}+{0,1}*S,
INDEX(TrnPrice,AGGREGATE(14,6,TrnNum/T/P,{1;2}))))

I couldn't think of any nice ways to do this in old style formulas, interested to see your formula...