Mar 24 2021 11:51 PM - edited Mar 25 2021 12:18 AM
Forty-five days after I posted my FIFO Inventory Formula Challenge and my solution thereto, here is another challenge to harden your array manipulation skills. As usual, we anticipate the valuable insights of @keenadvice @mathetes @Riny_van_Eekelen @Peter Bartholomew @Sergei Baklan, and @lori_m to enliven our learning journey.
Mar 25 2021 04:41 AM
@Twifoo Again, not going for the shortest one with 485 characters. Just a working formula :), though the end result has to be copied down and across. So, that might disqualify my contribution.
=LET(
sales,FILTER(TrnAmt,(CtmrName=K$22)*(TrnType=$J$2)),
salescount,COUNT(sales),
collections,SUM(FILTER(TrnAmt,(CtmrName=K$22)*(TrnType=$J$3))),
matrix,IF(SEQUENCE(salescount)>=TRANSPOSE(SEQUENCE(salescount)),1,0),
interim,MMULT(matrix,sales)-collections,
net,IF(interim<0,0,IF(interim>sales,sales,interim)),
dates,FILTER(TrnDate,(CtmrName=K$22)*(TrnType=$J$2)),
age,Cutoff-dates,
bracket,XLOOKUP(age,LowerBracket,$J$23:$J$26,,-1,-1),
result,SUMPRODUCT(net*(bracket=$J24)),
result
)
Mar 25 2021 06:01 AM
Mar 25 2021 06:13 AM
@Twifoo Thanks! And it's the best I can do using what I would call "out of the box" functions and some tricks inspired by what I found on-line.
Apr 01 2021 06:27 AM
The explanation of my legacy formula can now be read, and my solution file can also be downloaded, here:
Apr 01 2021 08:09 AM
@Twifoo Thanks Robert! Looked at your solution and admit I would never have been able to come up with such a formula. It is only thanks to the modern Excel functions that I have been able to solve problems like in your challenge. So, where does that put me? I like to think I'm pretty OK with the new tools, but I'm not so good with "old style" Excel. Like you, I'm also surprised that nobody else responded. Quite unlike your previous challenge.
Apr 01 2021 04:01 PM
I have worked through the problem (yet to look at other solutions). At the moment, I would say my solution is overweight and lacking elegance! It will produce a column array for each customer (I try to get one formula for an entire table but it is often not possible without Lambda recursion).
=LET(
receive, LET(
ac, SUMIFS(TrnAmt, CtmrName,@CName, TrnType,Type),
MMULT({1,-1},ac)),
crit, (CtmrName=@CName)*(TrnType=Sale),
Age, Cutoff - FILTER(TrnDate, crit),
Amt, FILTER(TrnAmt, crit),
Age′, TRANSPOSE(Age),
Accumulate, SIGN(Age′<=Age),
Difference, MINVERSE(Accumulate),
Accum, MMULT(Accumulate, Amt),
capped, IF(Accum<receive,Accum,receive),
outstanding, MMULT(Difference,capped),
Assign, (Age′>=LowerBracket)*(Age′<=UpperBracket),
byBand, MMULT(Assign, outstanding),
byBand)
Apr 01 2021 04:16 PM
Apr 02 2021 02:28 AM
Apr 02 2021 03:00 AM
Apr 02 2021 03:33 AM
Made it. I think this is my best endeavour.
=LET(
a,SUMIFS(TrnAmt,CtmrName,N,TrnType,"S*",TrnDate,">="&Cutoff-UpperBracket),
b,INDEX(a,4,)-SUMIFS(TrnAmt,CtmrName,N,TrnType,"C*"),
D,{1,0,0,0;-1,1,0,0;0,-1,1,0;0,0,-1,1},
MMULT(D,IF(a<b,a,b)))
The formula produces the 4x4 result as a dynamic array.
I suspect that I have used your problem as a learning exercise rather than strictly following the rules but thanks anyway.
Apr 02 2021 06:12 AM
Apr 02 2021 06:17 AM
Apr 02 2021 09:30 AM
Yes, you are perfectly correct. Two of the formulas are clearly dependent on there being 4 terms in the bracket. If 'I' were the name given to your bracket index and 'J' were its transpose, the matrix used for differencing would be
=(I=J)-(I=J+1)
Interestingly, but at the expense of more characters, pre-multiplying by
=--(J<=I)
would cause the columns of the second array to accumulate, whilst
=MINVERSE(--(J<=I))
would form the term within the DIFF operation.
In this instance, I chose to hard-wire the array constant
D, {1,0,0,0;-1,1,0,0;0,-1,1,0;0,0,-1,1},
and use 4 in place of MAX(I) to return the final row of the accumulated account array.
Apr 02 2021 09:35 AM
@Twifoo , thanks for another interesting exercise. I didn't take part since is quite busy these days and answer only on posts which takes not more than 5-10 minutes of my time. This one is definitely requires much more. Perhaps some later will try to train my brains even if I know some answers.
Apr 02 2021 01:18 PM
May I second @Sergei Baklan's vote of thanks for some innovative challenge problems.
The attached contains a version that is meant to work using standard Excel. It uses a strategy I employed before dynamic arrays came to Excel; that is, I used named formulas to hold the array formulas that I used all the time (this allowed me to avoid the effect of implicit intersection). Counting character length was not scientific; I tried to substitute the formula lengths to account for each occurrence of a name.