excelexcel-formulaexcel-2010formulaexcel-2007

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

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.

Sample result

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.

Sample data

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.

Solution

  • Here single function(♣) that will create a series of pro-rata multipliers (of appropriate length) for any given start/end date:

    EDIT: see end of soln for extended version per OP comment to original soln...

    SINGLE FUNCTION

    =J11*(LET(dates,EDATE(DATE(YEAR($K11),1,1),12*(SEQUENCE(1,YEAR($L11)-YEAR($K11)+2,1))),IF(dates<K11,K11,IF(dates<L11,dates,L11)))-LET(dates,EDATE(DATE(YEAR($K11)-1,1,1),12*(SEQUENCE(1,YEAR($L11)-YEAR($K11)+2,1))),IF(dates<K11,K11,IF(dates<L11,dates,L11))))/(L11-K11)

    Full function

    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)))

    First part - series of dates of appropriate length/span

    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)))

    Second part - shifted dates

    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)

    Third part: pro-rata yearly multipliers x total (£) cost

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

    *EDIT - EXTENDED VERSION

    Given above, the following extends this to align monetary results (1st table, o11:w21) within respective calendar period columns (spanning the entire period in question).

    This soln:

    1. Determines header row based upon the number of columns & corresponding calender periods (financial yrs commencing 1/1) as an array function (i.e. dynamic range)
    2. Utilises a modified version of the eq. provided for dates arrage (refer: "First Part", original soln)

    Comment - same caveats as before - i.e. Office 365 etc.

    Screenshot(s)/here refers:

    DATES (HEADER) - Y10 (array)

    =LET(y_,MIN(K11:K21),x_,MAX(L11:L21),EDATE(DATE(YEAR(y_)-1,1,1),12*(SEQUENCE(1,YEAR(x_)-YEAR(y_)+2,1))))

    Header function: start of each financial period spanning entire base period

    Comment - enter once within single cell Y10 - i.e. as an array function with Spill to right

    ALIGNED/SHIFTED FINANCIALS - Y11:Y21 (each cell in col is an array)

    =IFERROR(IF(Y$10#<EDATE(K11,-12),"",IF(Y$10#>EDATE(L11,12),"",INDEX(O11#,1,MATCH(Y$10#,EDATE(DATE(YEAR($K11)-1,1,1),12*(SEQUENCE(1,YEAR($L11)-YEAR($K11)+2,1))),0)))),"")

    Aligned financial pro-rata data table

    Comment - enter this as an array fn. (#SPILL! to the right) in each cell within column Y (can drag this function down Y11:Y21 as required)