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...
- keenadviceFeb 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}))))
- TwifooFeb 19, 2021Silver Contributor
You'll see my formula soon!
- PeterBartholomew1Feb 11, 2021Silver Contributor
Hi Lori
It shouldn't take you more than a few minutes to write a recursive Python script to reduce any LET function back to its simplest form (grin)! Whether the resulting formula is 'nice' or not is a matter of taste. Like beauty, simplicity is in the eye of the beholder. What others see as simplicity I see as primitive and unstructured, i.e. a classic demonstration of complexity!
BTW I reported the behaviour of my recursive formula that produced an array of results for the 5 products to Microsoft as an error. Oddly, although I could correctly output the array, I could not then count or sum the values it held (the start of the problem was that the 5 values were represented within 1 row?
- lori_mFeb 11, 2021Steel Contributor
Agree discussions around simplicity / complexity, etc. are pretty pointless given their subjective (and recursive!) nature. I was thinking of 'Nice' in a 'Pythonic' sense and it does seem Excel is heading in the direction of such languages.
I don't have access to LAMBDA to test but it should be possible to use recursion within a LET statement by doing something along the lines of this Python code:
Y = lambda f: (lambda x: x(x))(lambda y: f(lambda *args: y(y)(*args))) fib = lambda f: lambda n: (n if n<2 else f(n-1)+f(n-2)) >>> list(map(Y(fib),range(10))) [0, 1, 1, 2, 3, 5, 8, 13, 21, 34]
- PeterBartholomew1Feb 12, 2021Silver Contributor
I wouldn't say that I have reached the point of being able to read the concise syntax that seems to be favoured in the world of functional analysis with any degree of fluency! Even in the world of imperative computing I always preferred verbose to concise (Visual Basic to Java). The Y combinator still leaves me struggling to read what is the function definition and what arguments are being processed. With conventional math, which is somewhat concise, I instinctively feed in phases such as 'with respect to' but it has yet to happen with Curry's notation.
Despite that, I seem to have written a recursive Lambda function that I have applied to Twifoo 's challenge problem and then reapplied it to a simpler problem (that of calculating a 1D column that represents row totals of a 2D array) without change.
It might be that FOREACH implements the Y combinator and XSTACK the MAP function but, right now, I lack the clarity of thought to be sure. For the sake of completeness, my function to sum rows was
= LAMBDA(arr,num, SUM(INDEX(array,num,0)) )
Not totally awe inspiring, but useful.