Forum Discussion
Twifoo
Feb 08, 2021Silver Contributor
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...
keenadvice
Feb 19, 2021Brass Contributor
Fully accepting that I am too late to the party here...
...(and desperately trying not to look too hard at others' solutions in an attempt to see what I come up with that's either different or congruent)...
...how does this solution (at 231 characters) do Twifoo ?
=LET(
Crit,
(TrnType="Purchase")*(ProductName=J18),
LPQ,
INDEX(SORT(FILTER(TrnQty,Crit)),1),
PC,
SUM(Crit*TrnQty)-
SUM((TrnType="Sale")*(ProductName=J18)*TrnQty),
SUM(INDEX(SORT(Crit*TrnPrice,,-1),{1,2})*
CHOOSE({1,2},LPQ,PC-LPQ)))