+ Reply to Thread
Results 1 to 10 of 10

Distribute numbers based on date and complexity

  1. #1
    Registered User
    Join Date
    03-19-2019
    Location
    Europe
    MS-Off Ver
    Office 2016
    Posts
    11

    Distribute numbers based on date and complexity

    Dear Excelers,

    Today I have another thing which gives me headaches. I'm attaching you the file, what I would like to do on it, is to prepare a sort of forecast based on dates and complexity and to automatically fill the upcoming months and divide the total number for a specific phase/complexity to the difference between phases dates and if the difference is greater than 6 months just to distribute the numbers for maximum 6 months.

    I'm not sure if I my wish was clearly understood...

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,793

    Re: Distribute numbers based on date and complexity

    So, a lack of response on a question such as this implies some elements are not immediately obvious to the folk on the board...

    In an effort to get this moving can you clarify following :

    -- are the "day" values significant? your month headers are neither 1st nor last of the month but random (by month) -- same question re: phase dates
    -- you state that you want to apportion phase days by phase gap (with cap), but your expected results seemingly show phases 3 + 4 being done in parallel
    -- related to both of the above, can you confirm logic for your expected denominators - i.e. 3 for 28-May-20 to 29-Sep-20, but 4 for 21-Jan-21 to 21-Apr-21

    what I would say is that your 'ask' should prove straightforward enough once the above points are clarified.

  3. #3
    Registered User
    Join Date
    03-19-2019
    Location
    Europe
    MS-Off Ver
    Office 2016
    Posts
    11

    Re: Distribute numbers based on date and complexity

    Quote Originally Posted by XLent View Post
    -- are the "day" values significant? your month headers are neither 1st nor last of the month but random (by month) -- same question re: phase dates
    the day values are not significant for both headers (phase month) - interest is only for month

    Quote Originally Posted by XLent View Post
    -- you state that you want to apportion phase days by phase gap (with cap), but your expected results seemingly show phases 3 + 4 being done in parallel
    I did a mistake when filling the example - phase 3+4 shouldn't be done in parallel.

    Quote Originally Posted by XLent View Post
    -- related to both of the above, can you confirm logic for your expected denominators - i.e. 3 for 28-May-20 to 29-Sep-20, but 4 for 21-Jan-21 to 21-Apr-21
    for example shared by me the logic is the following:
    Phase 1 starts in May-20 ends just before phase 2 Sep-20 (August should be last month for distributing the numbers of phase 1 - but I see that is missing - my bad) ; same for next phases.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Distribute numbers based on date and complexity

    Administrative Note:

    Welcome to the forum.

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

    Thank you for helping us to help you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    03-19-2019
    Location
    Europe
    MS-Off Ver
    Office 2016
    Posts
    11

    Re: Distribute numbers based on date and complexity

    does somebody have any idea how to proceed here?

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,793

    Re: Distribute numbers based on date and complexity

    hi, sorry, I completely missed your earlier response.

    I'd suggest re-posting your original sample file, with results filled in for a couple of Projects -- which include any corrections (outlined in post #3)

    I confess I'm still a little unclear as to how you address Phase 4 as you have only the start date, so no duration period available (to act as denominator)

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    5,461

    Re: Distribute numbers based on date and complexity

    1) How to apportion last phase 4? There is no date in next phase to know how many months.
    2) There are missing months in row 3 (August 20; ...). Could you update again with right month and some manual inputs?

  8. #8
    Registered User
    Join Date
    03-19-2019
    Location
    Europe
    MS-Off Ver
    Office 2016
    Posts
    11

    Re: Distribute numbers based on date and complexity

    I've performed the update on a new version of initial file. Please find it below.
    One update - which now I've understood, phase 4 is the closure of the project so no hours will be spent out.

    Thanks guys.
    Attached Files Attached Files

  9. #9
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,793

    Re: Distribute numbers based on date and complexity

    OK - using your sample file, and values therein, one option would be:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the above will apply a cap of 6 months to any single phase - and uses phase 4 as end point for phase 3 {inclusive - per your expected results}
    note: your hour values in complexity matrix have decimals, hence attached results differ slightly to your manual results (which used visible integer)
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-19-2019
    Location
    Europe
    MS-Off Ver
    Office 2016
    Posts
    11

    Re: Distribute numbers based on date and complexity

    Wow, that is really amazing, at least from my perspective. Thank you very much. In the next following days, I will try to apply it on a bigger file to see how it fits.

+ 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. Material Stock Allocation based on order date & distribute value evenly
    By srglt332 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-15-2020, 07:14 AM
  2. Sequence numbers based on a Limit and Distribute :/
    By Aragorn911 in forum Excel General
    Replies: 5
    Last Post: 06-17-2019, 07:52 AM
  3. Replies: 0
    Last Post: 10-16-2016, 01:34 PM
  4. [SOLVED] Distribute Numbers
    By SBBmaster09 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-28-2016, 06:20 AM
  5. Distribute Whole Numbers Based on Percentages
    By Optimus21 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 06-03-2014, 07:00 AM
  6. Replies: 8
    Last Post: 03-25-2014, 07:37 AM
  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