Forum Discussion
Spilled Array based on different Time Periods and applied periods - Help Needed
I have another issue that I can't solve. Most info is in the attached example sheet I have a set of time periods start and end dates in rows across the top running from a few historic years to a few...
Peter, very cool stuff. Have seen many of your posts and always impressed. However, I haven't managed to apply this to my model yet. Obviously the example I sent was just a mock up so all my dates and everything are fully dynamic. But I'm having an issue testing your version when applying it. I have ported over all the lambda functions but getting an error so must have done something wrong. Somehow the function is seeing the dates along the top as it doesn’t work without them but I can’t see how it is referencing them. I need to spend more time on it.
The defined name Start_of_Period is hard-wired into the COUNTPMTλ function, where it is used to define the bins of the Excel FREQUENCY function. To avoid that would require Currying so that the worksheet formula becomes
= ext.MAPλ(Start_Date, End_Date, Applied_Periods, COUNTPMTλ(Start_of_Period))
and the Lambda function is
COUNTPMTλ = LAMBDA(timeline, LAMBDA(start, end, interval,
LET(
duration, 1 + DATEDIF(start, end, "m"),
nPmts, duration / interval,
counter, SEQUENCE(1, nPmts, 0),
pmtDate, EOMONTH(start, counter),
TOROW(DROP(FREQUENCY(pmtDate, timeline),1))
)
))
The additional LAMBDA means that the function required two sets of arguments, ordered left to right, before it evaluates. The first is provided explicitly in the worksheet formula whilst the second set are provided term at a time by the ext.MAPλ helper function.
Just in case a copy of the modified workbook is of value...