At 622 characters, our formula looks esoteric, but gradually becomes simple once you begin to understand the logical technique I used to manipulate the arrays. In this article, we will dissect the parts of such formula in the solution file.
The Circumstances of the Challenge
Covid Corp. sells vaccines at a maximum credit term of 56 days. After such period, Accounts Receivable are automatically converted to Notes Receivable bearing the legal rate of interest. Customers are quarantined and Covid Corp. expects no collection until 31-Mar-2021.
The Rules of the Challenge
The formula solution must comply with all of these rules:
No other name must be defined in the Name Manager, such that the formula must refer only to TrnNum, TrnDate, CtmrName, TrnType, TrnAmt, LowerBracket, UpperBracket, Cutoff, J23:J26, and K22:N22;
Without referring to any cell in G1:I5 and J1:N20, the formula in K23:N26 must return the Aged Receivables in K16:N19; and
Regardless of the Excel version of the user, the formula must be confirmed only with Enter.
The Objective of the Formula
The formula in K23:26 aims to return the Sum of the Positive Discrete Receivable Balances from the Customers in K22:N22 that fall within the Brackets in J23:26. For example, the formula in K26 aims to return the Sum of the Positive Discrete Receivable Balances from Covida that are more than (or equal to) 43 days old and less than (or equal to) 56 days old.
The Submitted Modern Formula
Last 24-Mar-2021, I posted my Receivable Aging Formula Challenge to the members of Microsoft Excel Tech Community. To my surprise, only Riny van Eekelen replied with this modern formula:
=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
)At only 485 characters, the foregoing formula is obviously shorter than mine. Nonetheless, the dynamic array functions used therein are available only to Microsoft 365 subscribers.
The Required Variables in our Formula
Our formula requires the determination of these variables:
Eligible transactions;
Remaining receivable balance;
Cumulative latest sales;
Discrete adjustments;
Negative discrete adjustments;
Discrete receivable balances; and
Positive discrete receivable balances.
The Eligible Transactions
To determine the eligible transactions, we use the formula below in K26:
=SUMPRODUCT((Cutoff-TrnDate>=INDEX(LowerBracket,$J26))*
(Cutoff-TrnDate<=INDEX(UpperBracket,$J26))*
(CtmrName=K$22)*(TrnType>"r")In the formula above, Cutoff-TrnDate returns the age of each transaction. The first INDEX returns 43, which is the 4th position in LowerBracket. Conversely, the second INDEX returns 56, which is the 4th position in UpperBracket. Finally, SUMPRODUCT returns a vertical array of 1's for eligible transactions (or 0's for ineligible transactions), as shown below:
={1;0;0;0;0;0;0;1;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}Notice that the 1's are in the 1st, 8th and 10th positions in the array and correspond to the first 3 sales to Covida.
The Remaining Receivable Balance
To determine the remaining receivable balance, we use the formula below in K26:
=SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))In the formula above, (CtmrName=K$22) returns a vertical array of 1's for Covida (or 0's for customers other than Covida). Moreover, -1^(TrnType<"r") returns a vertical array of -1's for Collection transactions (or 1's for Sale transactions). Finally, SUMPRODUCT returns 1200, which is the remaining amount receivable by Covid Corp. from Covida as at 31-Mar-2021, as shown below:
=1200The Cumulative Latest Sales
To determine the cumulative latest sales, we use the formula below in K26:
=SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r"))In the formula above, ">="&TrnNum coerces SUMIFS to return a vertical array of cumulative sales to Covida, starting from the last sale and 0's for transactions after the last sale to Covida. Thus, SUMIFS evaluates to:
={1600;1400;1400;1400;1400;1400;1400;1400;1090;1090;840;840;840;840;840;840;840;460;460;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}The Discrete Adjustments
To determine the discrete adjustments, we subtract the cumulative latest sales from the remaining receivable balance, as shown below:
=SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r")The foregoing subtraction evaluates to:
={-400;-200;-200;-200;-200;-200;-200;-200;110;110;360;360;360;360;360;360;360;740;740;1200;1200;1200;1200;1200;1200;1200;1200;1200;1200;1200;1200;1200;1200;1200;1200;1200}The Negative Discrete Adjustments
To determine the negative discrete adjustments, we filter the discrete adjustments by comparing whether they are less than zero, as shown below:
=(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r"))*
(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r")<0)The foregoing filter converts positive discrete adjustments to 0's, as shown below:
={-400;-200;-200;-200;-200;-200;-200;-200;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}The Discrete Receivable Balances
To determine the discrete receivable balances, we add the negative discrete adjustments to the transaction amounts, as shown below:
=TrnAmt+(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r"))*
(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r")<0)The foregoing addition evaluates to:
={-200;50;110;180;-100;-90;-70;110;160;250;310;380;460;110;130;160;380;200;460;380;460;550;130;160;200;250;460;60;550;160;550;650;250;780;650;760}The Positive Discrete Receivable Balances
To determine the positive discrete receivable balances, we filter the discrete receivable balances by comparing whether they are more than zero, as shown below:
=(TrnAmt+(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r"))*
(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r")<0))*
(TrnAmt+(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r"))*
(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r")<0)>0)The foregoing filter converts negative discrete receivable balances to 0's, as shown below:
={0;50;110;180;0;0;0;110;160;250;310;380;460;110;130;160;380;200;460;380;460;550;130;160;200;250;460;60;550;160;550;650;250;780;650;760}The Construction of our Legacy Formula
Given our foregoing discussions, we now construct our legacy formula by using the eligible transactions and the positive discrete receivable balances as the array1 and array2 arguments of SUMPRODUCT, as shown below:
=SUMPRODUCT((Cutoff-TrnDate>=INDEX(LowerBracket,$J26))*
(Cutoff-TrnDate<=INDEX(UpperBracket,$J26))*
(CtmrName=K$22)*(TrnType>"r"),
(TrnAmt+(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r"))*
(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r")<0))*
(TrnAmt+(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r"))*
(SUMPRODUCT(TrnAmt,(CtmrName=K$22)*-1^(TrnType<"r"))-
SUMIFS(TrnAmt,TrnNum,">="&TrnNum,CtmrName,K$22,TrnType,">r")<0)>0))The array1 and array2 arguments of SUMPRODUCT evaluate as follows:
=SUMPRODUCT({1;0;0;0;0;0;0;1;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},{0;50;110;180;0;0;0;110;160;250;310;380;460;110;130;160;380;200;460;380;460;550;130;160;200;250;460;60;550;160;550;650;250;780;650;760})Finally, SUMPRODUCT returns 360, which is the sum of 110 and 250, as shown below:
=360The Copy of the Solution File
Did our legacy formula blow your mind? Kindly inform me of your thoughts on the solution file through a comment below.