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...
lori_m
Feb 11, 2021Steel Contributor
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...
keenadvice
Feb 19, 2021Brass Contributor
I think maybe these perhaps are the 'winning' solutions here???
It's hard to see how they could be shorter...
Well done lori_m
- SergeiBaklanFeb 20, 2021MVP
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.
- lori_mFeb 19, 2021Steel Contributor
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.- keenadviceFeb 20, 2021Brass Contributor
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}))))- lori_mFeb 20, 2021Steel Contributor
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.
- TwifooFeb 19, 2021Silver Contributor
You'll see my formula soon!