Forum Discussion
djclements
Sep 22, 2025Silver Contributor
Rack of Lambda
There’s been a lot of content shared recently to commemorate the upcoming 40th anniversary of Microsoft Excel. Personally, I’ve only been using Excel for around half that time, but October also marks...
djclements
Sep 23, 2025Silver Contributor
Glad to hear it! Thanks for the feedback.
Harun24HR
Sep 24, 2025Bronze Contributor
Hi, can I ask for help? In attached file you will find my input and desired output. Basically, I want to breakdown payments into single records for each month. Can we make a generalize lambda formula so that I will just input full data set range and start date, end date as input parameter and the formula will give the desired output?
- djclementsSep 24, 2025Silver Contributor
As a generalized function, perhaps something along these lines:
PMTSUMMARY = LAMBDA(array,start_date,end_date,[interval],[int_type], LET( interval, IF(ISOMITTED(interval), 1, interval), int_type, IF(ISOMITTED(int_type), 2, int_type), a, IF(ISOMITTED(array), SEQUENCE(ROWS(start_date)), array), n, IF(int_type = 1, end_date - start_date, (YEAR(end_date) - YEAR(start_date)) * 12 + MONTH(end_date) - MONTH(start_date)), m, SEQUENCE(, AGGREGATE(14, 6, QUOTIENT(n, +interval), 1) + 1, 0) * IFS(interval > 0, interval), j, CHOOSE(int_type, start_date + m, EDATE(+start_date, m), EOMONTH(+start_date, m)), t, end_date >= j, λ, LAMBDA(x, TOCOL(IFS(t, x), 2)), HSTACK(IF(COLUMNS(a) = 1, λ(a), CHOOSEROWS(a, λ(SEQUENCE(ROWS(a))))), λ(j)) ) )
[interval] (optional) - the number of intervals between payments (default is 1)
[int_type] (integer, optional) - specifies the interval type
- 1 - Days
- 2 - Months (default)
- 3 - Month Ends
See attached examples...
- Harun24HRSep 24, 2025Bronze Contributor
Fantastic! It works fine to me. Let me test with my real data and feedback if find any issues.