Forum Discussion
Receivable Aging Formula Challenge
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
)
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.
The explanation of my legacy formula can now be read, and my solution file can also be downloaded, here:
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.