May 16 2023 03:46 PM
I have a long formula that ultimately boils down to the following:
IF(INDEX(O_SU_Capitalization, 1, Mv) = "Capitalized",
BYROW(FILTER(TotalInterest, YEAR(CurrMo) = CurrYr, 0), LAMBDA(S, SUM(S))),0) + BYROW(FILTER(MonthlySpend, YEAR(CurrMo) = CurrYr, 0), LAMBDA(S, SUM(S)))
For context, the variables, TotalInterest and MonthlySpend are arrays calculated within this same formula using the LET function. The two arrays are monthly tables of total monthly spending plus interest incurred on a line of credit for a construction loan.
Here is a brief summary of the other variables:
O_SU_Capitalization is a table of values indicating whether the interest on each loan is rolled into the loan ("capitalized") or not.
Mv is a Match(Sequence(N), Sequence(N)) formula, where N is the number of projects in the portfolio.
CurrMo is a Sequence formula generating the list of months in the timeline
The purpose of the portion of the formula that I copied above is to summarize the total spending with interest per year, using the FILTER function. The variable CurrYr refers to a cell that has a year value in it. The resulting table displays the projects by row and the years by column, like this:
In order to make it work, I have to drag the formula to the right, copying it in each column so that it refers to the next year in the timeline. I originally tried making CurrYr a SEQUENCE function, hoping that it would filter and sum the values for each year, spilling them automatically in the columns to the right, but it resulted in an error.
I am wondering if there is a way to use a Lambda function (recursive or otherwise) or some other means to produce the above result without having to manually copy the formula for each year in the table. The reason is because this formula is quite long, and just copying the formula across all of the years in the table adds almost half a MB to the spreadsheet's size and slows down the calculation significantly.
May 18 2023 09:29 AM
May 18 2023 10:33 AM - edited May 18 2023 10:42 AM
Have you tried making the formula a Named Lambda function? then you don't copy the whole formula just something like =LambdaName(Yr) but I doubt that extra size and/or speed hit will be fixed by that. Furthermore you can then try calling that Lambda using MAP or BYCOL. Again, i suspect the speed hit won't be fixed that way.
i think to fix the speed you might try adjusting the formulas you are using. For example you are doing a SUM of a Filter, what about a SUMIFS? or old school with SUMPRODUCT( (condition)*(values) )
Or maybe just rearrange the order of the formula so you only do 1 BYROW and only 1 FILTER something like but again I don't understand why SUM and FILTER. Are TotalInterest and MonthlySpend 2d arrays each? :
BYROW(FILTER(HSTACK(TotalInterest, MonthlySpend), YEAR(CurrMo) = CurrYr, 0),
LAMBDA(S, SUM(TAKE(S,COLUMNS(TotalInterest))*(INDEX(O_SU_Capitalization, 1, Mv) = "Capitalized"),TAKE(S,-COLUMNS(MonthlySpend)) )) )