Forum Discussion
FIFO Inventory Formula Challenge
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...
Could someone explain me what is the benefit of using in LET() as short names as possible ? IMHO, that only adds more headache both for debugging and maintenance stage.
That's a nice approach and I think it could be made as short if not shorter than mine by using single character names and separate criteria for TrnType and ProductName.
SergeiBaklan
Thanks for the kind words, I have certainly learned much from from reading through your posts particularly on the power query side. The CHAR(173) trick for distinguishing numeric strings in text criteria was stumbled upon quite by accident - it arose from being confused about some code that was sorting HTML data and it turned out there were these hidden hyphen characters in the string which were ignored in some situations but not others.PeterBartholomew1
Going back to your earlier point, for writing more efficient code that doesn't create extra stack space, I can't test in Excel but was able to adapt the "tail recursion" tutorials to work with lists as parameters. and I believe could be a promising approach.lori_m I've tried a couple of different angles on it, and sweated the characters out a bit, but yours still wins:
@keenadvice attempt 1 now 206 characters
=LET(
A,(TrnType="Purchase")*(ProductName=J18),
B,INDEX(SORT(FILTER(TrnQty,A)),1),
C,SUM(A*TrnQty)-SUM((TrnType="Sale")*(ProductName=J18)*TrnQty),
SUM(INDEX(SORT(A*TrnPrice,,-1),{1,2})*CHOOSE({1,2},B,C-B)))@keenadvice attempt 2 191 characters
=LET(
A,(ProductName=J18),
B,(TrnType="Purchase")*A,
C,SORT(CHOOSE({1,2},TrnPrice,TrnQty)*B,,-1),
D,INDEX(C,1,2),
INDEX(C,1,1)*D+INDEX(C,2,1)*(SUM(B*TrnQty)-SUM((TrnType="Sale")*A*TrnQty)-D))@lori_m 172 characters
=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}))))That second attempt could be further reduced by using 'IF({1,0}' in place of 'CHOOSE({1,2}' which then beats the first formula I posted by one character. The second formula I posted was basically just using the same formulas Twifoo used on the sheet but I prefer the SORT method which I think makes things a bit clearer.
You'll see my formula soon!
