+ Reply to Thread
Results 1 to 12 of 12

How to distribute a value to specific months

  1. #1
    Registered User
    Join Date
    07-14-2020
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    4

    How to distribute a value to specific months

    Hi All,

    I am looking to use a formula to help distribute costs into a cashflow spreadsheet. I have attached a sample spreadsheet, ideally i would like to be able to have the value from cell D5 broken into different percentages and then distributed to months in the cashflow.

    As an example to make payments to a builders the payments might be broken in 10% in month 1, 25% in month 3, 40% in month 8, 15% in month 11 and 10% in month 14. Example 1

    If this isn't possible could I nominate a month that each payment would be made in and if there is no payment in that period then a $0 would be returned. Example 2

    Kind Regards

    Wade
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to distribute a value to specific months

    Welcome to the forum.

    Please add expected results (calculated manually) to the sample workbook to show examples of the outcomes you are looking for, then post the workbook afresh.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: How to distribute a value to specific months

    You need to give your desired solution in the example. Which months and which percentages

    How do we know what % to apply
    Will there be more than one project on the sheet, so start month is relevant

    It can be done, its just that you are ot clear in how you want it done

  4. #4
    Registered User
    Join Date
    07-14-2020
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to distribute a value to specific months

    Hi,

    I have updated the spreadsheet to try and better represent what I am trying to achieve.

    For the construction costs, they would be ideally broken down by percentage and allocated to the specified month.

    The landscaping cost would begin in the period start month and be spread over the range of months.

    I would like the cashflow spreadsheet to use the input page as the driver and represent all of the updates that are made so each cell in the row would need the formula.

    Kind Regards
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to distribute a value to specific months

    What happens if the period start for the build cost is not 1?

    Is the formula supposed to work out whether the payments need to be equal or based on a percentage, and if so, is the identifying factor merely the population of the range column?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to distribute a value to specific months

    It's a monster, but seems to work (see attached):

    =IFNA(IF(AND(INDEX(Input!$G$3:$G$4,MATCH($A5,Input!$A$3:$A$4,0))=0,INDEX(Input!$F$3:$F$4,MATCH($A5,Input!$A$3:$A$4,0)) < =C$2),INDEX(Input!$B$12:$B$16,MATCH(C$2-INDEX(Input!$F$3:$F$4,MATCH($A5,Input!$A$3:$A$4,0))+1,Input!$C$12:$C$16,0))*$B5,IF(AND(INDEX(Input!$F$3:$F$4,MATCH($A5,Input!$A$3:$A$4,0)) < =C$2,C$2 < INDEX(Input!$F$3:$F$4,MATCH($A5,Input!$A$3:$A$4,0))+INDEX(Input!$G$3:$G$4,MATCH($A5,Input!$A$3:$A$4,0))),$B5/INDEX(Input!$G$3:$G$4,MATCH($A5,Input!$A$3:$A$4,0)),"")),"")
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-14-2020
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to distribute a value to specific months

    Thanks very much it looks great.

    Could you explain the formula to me in what it is doing, I need to do this to a whole spreadsheet and would like to understand the concept behind it?

    Regards

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to distribute a value to specific months

    OK - you can use Evaluate Formula to step through and see what it's doing, however i will write a summary. It will take a little while, though! I'll post back when I have done it.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to distribute a value to specific months

    Here goes:

    IF(AND(INDEX(Input!$G$3:$G$4,MATCH($A5,Input!$A$3:$A$4,0))=0,INDEX(Input!$F$3:$F$4,MATCH($A5,Input!$A$3:$A$4,0)) < =C$2),

    If there is no value in the Input | Range field is blank (0) AND The Input | Period Start field is less than or equal to the column header …

    INDEX(Input!$B$12:$B$16,MATCH(C$2-INDEX(Input!$F$3:$F$4,MATCH($A5,Input!$A$3:$A$4,0))+1,Input!$C$12:$C$16,0))*$B5,

    … find the percentage payment amount in the construction table by subtracting the Input | Period Start figure from the column header value plus 1 and matching the resulting value with the month value in the construction table – multiply this by the value in B5 (the total). This deals with anything that’s a build cost.

    IF(AND(INDEX(Input!$F$3:$F$4,MATCH($A5,Input!$A$3:$A$4,0)) < =C$2,C$2 < INDEX(Input!$F$3:$F$4,MATCH($A5,Input!$A$3:$A$4,0))+INDEX(Input!$G$3:$G$4,MATCH($A5,Input!$A$3:$A$4,0))),

    If the Input | Period Start value is less than or equal to the column header value and the column header value is less than the Input | Period Start value plus the Input | Range value …

    $B5/INDEX(Input!$G$3:$G$4,MATCH($A5,Input!$A$3:$A$4,0)),"")),

    … divide the B5 value by the Input | Range value.

    =IFNA(…,"")

    If neither set of requirements fits, return blank.
    Last edited by AliGW; 07-15-2020 at 07:49 AM.

  10. #10
    Registered User
    Join Date
    07-14-2020
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How to distribute a value to specific months

    Hi,

    Thank you very much for your assistance, that was a huge formula.

    I have tried to understand it and apply it to the rest of the spread sheet. I failed.

    Could i email you the full version of the spread sheet with a description of what I want to achieve as I have tried to simplify the whole spreadsheet?

    Regards

  11. #11
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: How to distribute a value to specific months

    If you put up the cost breakdown in a table, you could simplify the formula by using VLOOKUP and MATCH functions.

    See highlighted area in attached file.

    With the cost breakdown in a table, you also have an overall view of the (payment) progress of each stage of construction.
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: How to distribute a value to specific months

    Could i email you the full version of the spread sheet ...
    No, that’s not allowed - sorry. Everything should stay here fir the benefit of all.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 01-26-2017, 02:19 AM
  2. [SOLVED] Distribute specific amount to months
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-23-2016, 10:45 AM
  3. Distribute revenue across months
    By Carolinanilorac in forum Excel General
    Replies: 3
    Last Post: 10-26-2015, 01:28 AM
  4. Distribute revenue across months
    By Carolinanilorac in forum Tips and Tutorials
    Replies: 1
    Last Post: 10-25-2015, 08:55 PM
  5. Distribute a value with specific criteria to a data range
    By Barni01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-07-2014, 05:29 AM
  6. Replies: 3
    Last Post: 10-30-2014, 02:13 PM
  7. Replies: 3
    Last Post: 10-23-2012, 09:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1