Forum Discussion
Receivable Aging Formula Challenge
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 PeterBartholomew1 SergeiBaklan, and lori_m to enliven our learning journey.
15 Replies
May I second SergeiBaklan'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.
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)- I appreciate your humble admission. I am certain you could improve your formula by perhaps avoiding the repetition of LET.
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.
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 )- Your solution is an admirable combination of multiple functions!
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.
