Excel formula to split an amount per year depending on expenditure days within a date range-CodePudding

I am in the process of building a formula to split a total cost (in column J) based on start and end expenditure periods that can vary from 2021 to 2031. Based on the days between the expenditure period dates (column M), I managed to work out to split the cost using the formulas below up to 2023 but it is not consistent and at times incorrect.

In cell P5 I have the following formula. For year 2021, I seem to get the correct split result. =IF($K5>AS5,0,$J5/$M5*(AS5-$K5))

In cell Q5, I have the following formula. For year 2022, I seem to get the correct spit as well =MIN(IF(SUM($N5:P5)>=$J5,0,IF($L5>=AS5,$J5/$M5*(AS5-AR5),$J5/$M5*($L5-MAX(AR5,$K5)))),K5)

However, I don't get the right result in cell Q6 which has the same formula but different dates =MIN(IF(SUM($N6:P6)>=$J6,0,IF($L6>=AS6,$J6/$M6*(AS6-AR6),$J6/$M6*($L6-MAX(AR6,$K6)))),K6)

Cell R6 shouldn't return any result because it is out of date range. This is where things get mixed up.

Note that from column AR to BC, it is all year end dates from 2020 to 2031 as shown below.

Is there a better way to tackle this sort of formula as I seem to get dragged into a long and unreliable way of doing this.

CodePudding user response：

It may appear somewhat unwieldy in length, but it is far more robust (and concise) compared to the combination of steps/series you have created. What's more, it returns the precise answer RE: pro-rata payments and is guarenteed to never over/under-run RE: total payment (by design).

BREAK-DOWN

Comprises 3 distinct parts (some of which are similar in pattern/formation):

1] First part - create a series (array) of years spanning start-end dates:

=LET(dates,EDATE(DATE(YEAR($K5)-1,1,1),12*(SEQUENCE(1,YEAR($L5)-YEAR($K5) 2,1))),IF(dates<K5,K5,IF(dates<L5,dates,L5)))

Thanks to the lovely Spill functionality the new Office 365 variant Excel boasts, you never have to worry about how many years are required -- so long as you have the space to the right of this workbook (would be unusual otherwise - assuming you start in column O and clear any content to the right of this, you'd need an end date beyond the year 2557 (26th century) to run out of columns! ☺

2] Second part is merely a replica of the firs series, albeit shifted to the right 'once' (so starts with the 2nd element in the 1st series):

=LET(dates,EDATE(DATE(YEAR($K5),1,1),12*(SEQUENCE(1,YEAR($L5)-YEAR($K5) 2,1))),IF(dates<K5,K5,IF(dates<L5,dates,L5)))

3] Third part - you have the basic ingredients from parts 1 and 2 to complete the required task easily: simply deduct series 2 from 1 (giving days between successive dates in series 1 - i.e. days for each year between start and end dates), divide by total days (to yield pro-rata multipliers), and then multiply these by the total £amount and voila - you have your series!

=J5*(O6#-O5#)/(M5)

♣ Caveat(s) - assuming you have Office 365 compatible version of Excel (which is quite common nowadays)