Forum Discussion
Recursive Lambda Filter Function
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)) )) )
- Yes, both TotalInterest and MonthlySpend are 2d arrays. Each row in the arrays represent different projects in the portfolio. Each column represents the value (either interest cost incurred or the monthly draw) for each month in the timeline of each project.
- Did any of those ideas help? Can't really help more unless you share a workbook (with not private info please).
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.).
