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 in...
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.
The explanation of my legacy formula can now be read, and my solution file can also be downloaded, here:
