Forum Discussion
Receivable Aging Formula Challenge
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)
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.
- I also suspect that your formula use of the array constants will become inapplicable when the data is modified.
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.
- Now as I understand the problem, I think I have an idea for an improved solution making better use of the initial SUMIFS. I won't look at your solution yet!
