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)) )) )
Jun 06 2023 09:34 AM
Jun 06 2023 09:59 AM
Jun 06 2023 01:23 PM
@mtarler I was not able to get it to work, but I may not have applied it correctly. I have attached a simplified version of my spreadsheet, where I have taken out all other formulas other than the one in question, which can be found in the CPI tab.
I have simplified this a bit, as this is only calculating the Total Interest by year, and excludes the calculation of construction spending by year. But apart from that simplification, you'll see that this is the full formula that I mentioned in my original post as being very long. If you were to call the penultimate variable, which is "TotalInterest" you would see that it would spill into a single table. These values would be the interest accumulated by month for each project. However, the final line of the algorithm is the one where I am trying to sum up the TotalInterest by year, rather than by month, and this is where I could not get it to spill, because I could not feed a series of values into the filter function for the Current Year (e.g., filter for the first year in the array and print the results in the first column, and then filter for the second year in the array and print the results in the second column, etc.).