Forum Discussion

James_Buist's avatar
James_Buist
Brass Contributor
Oct 11, 2024

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...
excel
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 12, 2024

James_Buist 

I was going to leave this one to David, but somehow I got embroiled with some interesting results.  Firstly, I do not think I have ever worked with a variable pitch timeline before so generating it was a challenge; I am still not sure what is the most economical way of specifying it.

 

Turning to the expenses, I wrote a function that returns the payment dates for a single expense

COUNTPMTλ = 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, Start_of_Period),1))
  )
)

I was planning to assign each payment to a time period and use GROUPBY to accumulate frequent payments over the longer reporting intervals.  Then it occurred to me that using the timeline as FREQUENCY bins would achieve the same goal.  Hence the function as it is.

 

My next issue was that there are multiple expense lines.  The first thought is to use MAP to run over the various lines.  Except, that returns a nested array error (in common with every other worthwhile problem I have ever seen - OK, so that's probably an exaggeration!)  That is where I turned to a function MAPλ I had written earlier and posted to GitHub as a Gist.

 

The formula I finished with was

= ext.MAPλ(Start_Date, End_Date, Applied_Periods, COUNTPMTλ)

where ext is a module that I created on this occasion to hold Excel extensions.

 

 

cc djclements 

James_Buist's avatar
James_Buist
Brass Contributor
Oct 12, 2024
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.
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 12, 2024

    James_Buist 

    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.

Resources