Forum Discussion
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...
How do you generate these exercises? Amazing!
Here I again didn't understand the logic behind (From Last Purchase Qty), just imitate your formula. First attempt
=LET(
type, -(EVEN(TrnType="Sale")-1),
prod, (ProductName=J18),
table, CHOOSE({1,2,3,4},TrnNum,TrnQty*type, TrnPrice, type*prod),
total, SUM(INDEX(FILTER(table,prod),0,2)),
two, INDEX(SORT(FILTER(table, prod*type=1),,-1),{1;2},{2,3}),
price, INDEX(two,,2),
lastQty, MIN(total, INDEX(two,1,1)),
QTYs, CHOOSE({1,2}, lastQty, total-lastQty),
MMULT(QTYs,price) )If logic will be more clear perhaps will do another attempt in few days.