Forum Discussion
Receivable Aging Formula Challenge
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.
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.
