Forum Discussion

Copper Contributor

Sep 27, 2022

Dynamic formula that splits/distributes revenue a value among various monthly start and end dates

https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.png **similar to this** trying to figure out a single formula that will allow me to split up the value from Column A across columns B-Z with d...

excel

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Sep 28, 2022

John_Bloggs650

You wrote: "on the file that you uploaded the start date in B1 starts 9/6/2020 however the formula does not begin to fill until Column Z or Oct-20 (* a month after)"

Well, that's because you weren't clear. The formula I used splits the contract value into equal terms. If a 12 month contract starts and ends somewhere during a month, either the first or the last month will have to be skipped. Now I understand that your intention is to split it into 13 periods. A partial first month, then 11 whole months and a partial 12th month. But do you also want the 11 whole months to vary based on the number of days in each month?

Pasted your formula in my own file (with adjusted references) and it doesn't seem to make any sense. I must be doing something wrong. Can't really test it without seeing it at work in your file. Can you upload it or else share it via OneDrive or similar?

John_Bloggs650

Copper Contributor

Sep 28, 2022

Riny_van_Eekelen

Hi to Answer your question even if the date encompasses 1 day out of the month, I would still like to include and round it up to the whole month and spread it across evenly.

this is the table Ive been trying to do the transfer test from, Column, 4 Row 2 formula: =IF(AND(D$10<=$B11,D$10>=$A11),SUM($C11/(SUM($B11-$A11)+1)),"")

Start	Finish	Value	24-Feb	25-Feb	26-Feb	27-Feb	28-Feb	1-Mar	2-Mar	Mar-22	Mar-22
24-Feb	26-Feb	15.57	5.190	5.190	5.190
25-Feb	28-Feb	26.37		6.593	6.593	6.593	6.593
26-Feb	27-Feb	10			5.000	5.000
27-Feb	1-Mar	30.15				10.050	10.050	10.050
28-Feb	2-Mar	40.66					13.553	13.553	13.553
Feb-29	1-Mar	7.5

Is there a way to pull only the month from the various dates in the first 2 columns that include individual days to use across the other columns as just months?

Please see attached

Data:

https://docs.google.com/spreadsheets/d/1XpKT2NJCe0S8d9hbCcMlB1KwkHJtvi05/edit?usp=sharing&ouid=113886818105478529954&rtpof=true&sd=true

Example

https://docs.google.com/spreadsheets/d/1gN7PZASv7hUj4y-5bdnAP9KuK1XPMq4v/edit?usp=sharing&ouid=113886818105478529954&rtpof=true&sd=true

Riny_van_Eekelen

Platinum Contributor

Sep 28, 2022

John_Bloggs650 Now you have lost me. In your example you split by day.

John_Bloggs650

Copper Contributor

Sep 28, 2022

sorry for that. Only using it as a rough example. Ideally I would like to use months but could not quite manipulate it

PeterBartholomew1

Silver Contributor

Sep 28, 2022

John_Bloggs650

The function was extracted from a workbook that used a month-based timeline with period start and period end dates (the 1st and 28/29/30/31st respectively). It could be that would be more use to you.

Some functions:

Addλ = LAMBDA(x, y, x + y);

Timelineλ = LAMBDA(modelStart, duration, [option],
    LET(
        numberPeriods,  duration*MPY,
        counter,        SEQUENCE( , numberPeriods),
        periodEnds,     EOMONTH(modelStart, counter-1),
        periodStarts,   1 + EOMONTH(periodEnds, -1),
        SWITCH(option, 0, periodStarts, 1, periodEnds, counter)
    ));

UniformSpreadλ = LAMBDA(amount, eventStart, [duration],
    LET(
        months, IF(ISOMITTED(duration),1,duration),
        eventFinish,    EOMONTH(eventStart, months - 1),
        withinPeriod,  (modelPeriodBegins# >= eventStart) * 
                       (modelPeriodEnd#    <= eventFinish),
        IF(withinPeriod, amount/months, 0)
    )
);

SeasonallyAdjustedλ = LAMBDA(base, adjustment,
    LET(
      appliedSeasonalityAdj, INDEX(adjustment, MONTH(modelPeriodBegins#)),
      appliedSeasonalityAdj * base
   ));

StepIncreaseλ = LAMBDA(base, ratio, eventStart,
    IF(modelPeriodBegins#<eventStart, base, base*ratio)
);

The book also had a function to accumulate a balance from inflows and outflows and apply interest payment on positive balances but that is well out of scope here.