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.

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.

Originally Posted by XLent

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

Originally Posted by XLent

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

Originally Posted by XLent

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

does somebody have any idea how to proceed here?

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)

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.

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

Formula:

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)

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.

I'm coming back to the topic, seems that I've had a wrong appreciation of data and I have some situation where the distribution period needs to be longer than 6 months, and I need to have 24 months, which means that the hours will be distributed for the whole period.
Can you please help me with an update of formula, I've banged my head trying to do it by myself - but failed in the end.

below assumes you simply wish to swap out the cap from 6 to 24 months, rather than extending life of each phase to 24 months by default.

Formula:

Please Login or Register  to view this content.

if this isn't doing what you need post a further sample (with expected results) which better illustrates the revised requirements.

Finally I've come back with a revised file, and rows with yellow are filled as an example. I'm pretty sure that this will be the final example. Can you please once more help with filling the formulas.Thanks in advance.

the below approach largely mirrors your expected results with following exceptions:

1. some of your projected results are incorrect - either based on precision, or incorrect values relative to complexity value (e.g. row 70)
2. using the LOOKUP approach it won't really be feasible to return the "wrong milestone" values, just blank

w.r.t #2 I would suggest using conditional formatting on the dates themselves, to flag these types of anomalies.

Formula:

Please Login or Register  to view this content.

all of the above embedded within attached

Sorry for late replying, it works perfectly. Many thanks!!!!