• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MS Project Work Contours in Excel

A

angryblackman

New Member
I'm trying to replicate (simply so I have a bit more control via Excel) some functionality from MS Project in Excel.


Background:

MS Project allows you to change how work is scheduled for a task using work contours

ie: if you have a task that will take 40 hours of effort and it's duration is 10 days by default Project will schedule a single resource 4 hrs a day for 10 days and that is a Flat Work Contour


You can assign a different Work Contour and Project will change the schedule effort to fit according the shape of the new Work Contour. (see .jpg)


What I'd like:

Is given a task effort and duration (ie: 100 hrs and 5 weeks), be able to have Excel distribute hours per week according to a work contour.


I think this a Math & Excel function problem, but I cannot figure it out. Any one have any ideas?


Thanks in advance

(i've attached a spreadsheet and image from MS Project).

The xlsx has a second tab that has a normdist formula which generates a bell curve distribution which I think is a step in the right direction


https://dl.dropbox.com/u/31649473/chandoo_work%20contour_help.jpg

https://dl.dropbox.com/u/31649473/chandoo_work%20contour_help.xlsx
 
Hi, angryblackman!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Very well explained the different types of work countour and nice developing of the bell shaped distribution. Would you please post or upload an actual example indicating manually desired output? Thank you.


Regards!
 
Hi,

Depending on the type of contour you wish to calculate (front-loaded, back-loaded, flat, etc.), you will need different formulas.


I have made the following assumptions:

The hours info is in cell A1

the duration info is in cell B1


For a flat contour, where the hours are divided equally among the time periods, you could use the following formula:

=(ROW($A$1:INDEX($A:$A,$B2))^0)*($A2/$B2)


For a back-loaded contour, where most of the hours are at the end, you could use the following formula:

=ROW($A$1:INDEX($A:$A,$B2))*($A2/($B2*($B2+1)/2))


For a front-loaded contout, where most of the hours are at the beginning, you could use the following formula:

=LARGE(ROW($A$1:INDEX($A:$A,$B2)),ROW($A$1:INDEX($A:$A,$B2)))*($A2/($B2*($B2+1)/2))


For additional contours, we could construct arrays using a similar approach.


To load these values into specific duration columns, you could use an approach similar to the following:

Assuming that your weeks are in cells E1:I1, and the following formula will work in cell E2. (This formula uses the back-loaded contour.)


=IFERROR(INDEX(ROW($A$1:INDEX($A:$A,$B2))*($A2/($B2*($B2+1)/2)),MATCH(E$1,$E$1:$I$1,0),1),0)

(Essentially, it is trying to match the column heading in the list of column headings, and determining the correct array value to display.)


You could apply desired rounding of values (e.g. using the ROUND() function) as desired.


Cheers,

Sajan.
 
Hello,


I had a couple of quick minutes... so I thought I would supply one more formula.


Here is the formula for the "bell" contour. (This mimics the sine wave.)

Assuming that the hours value is in A2, and duration is in B2:

=(SIN(RADIANS((ROW($A$1:INDEX($A:$A,$B2))*(180/(B2+1))))) /SUMPRODUCT(SIN(RADIANS((ROW($A$1:INDEX($A:$A,$B2)))*(180/(B2+1)))))) * A2


(Again, you can round the resulting values, etc. as desired.)

The first part of the formula calculates the "sin" values for 180 degrees, with number of intervals based on the number of durations.

The second half of the formula is used to calculate the sin values as a percentage of total.

The percentage values multiplied by the "hours" data provides the array of values in the "bell" contour.


You could use the same approach I outline in my previous post to display the specific values for a given unit of time.


In MS Project, I noticed a few other contours listed (such as early peak, late peak, turtle, etc.). I have never used them. However, if you need to use them, I am sure we can figure out the formulas for them as well.


Cheers,

Sajan.


P.S. I don't have a way to see the files you uploaded due to firewall restrictions on my side. However, I am able to read what you post in this forum.
 
Thanks Sajan.


I'm sure it's at least a lack of caffeine this morning, but I can't quite figure out (I'm starting at the first example and working my way down) your flat contour example.

Q: What are you expecting in Cell B2?


if I assume that headings are in Row 1 with your example, fill that formula across in '#' of columns it works how I would expect -- ie I change cells A2 or B2 and get correct forecasts (assuming that I adjust the number of cells that formula is filled across)


like this

'effort duration wk 1 wk 2 wk 3 wk 4 wk 5

100 5 20 20 20 20 20'


am I understanding your suggestion for a flat curve correctly?


FYI: this was the example that I had included in the Excel files I had linked to in my first post

'work contour type wk 1 wk 2 wk 3 wk 4 wk 5

Flat 20% 20% 20% 20% 20%

Back-Loaded 10% 15% 20% 25% 30%

Bell 10% 25% 30% 25% 10%

Front-Loaded 30% 25% 20% 15% 10%

Late Peak 10% 15% 30% 25% 20%

Double Peak 10% 30% 20% 30% 10%

Early Peak 20% 30% 25% 15% 10%

Turtle 12.5% 25% 25% 25% 12.5%'


I know I'm thinking about this very simplistically, but what this represents to me is with a duration of 5 time periods (days/weeks/months, whatever...), for each contour type - this is what the percentage of hours that would be forecast for each time period.


What I wasn't sure about was how to generate those percentages for variable (ie-different) time periods.


anyways I'll take some time and have a look at your suggestions for the other contours.


Thanks so much!


.Brad
 
Hi Brad,

In my original post, I inadvertely stated that Hours was in A1 and Duration was in B1. I meant to say that the Hours was in column A, and Duration was in column B. The formula I supplied assumes that the Hours is in A2, and Duration in B2 (with cell A1 and B1 being headings). Sorry about that.


Using the "flat" countour formula, if the hours value is in cell A2, and the duration value is in cell B2, the following will provide you an array that shows the breakdown of the hours for each period in the duration:

=(ROW($A$1:INDEX($A:$A,$B2))^0)*($A2/$B2)

In your example, you had 100 in cell A2, and 5 in cell B2.

The formula will return the following array:

{20;20;20;20;20}


To convert this into percentage values, you could divide the formula by the value in A2.

i.e. =((ROW($A$1:INDEX($A:$A,$B2))^0)*($A2/$B2)) / $A2

and you would get {0.2;0.2;0.2;0.2;0.2} indicating 20% each.


(To view the array values, you will need to put the formula in a cell, and hit "F9" to see the values.)


You can then use this array in a lookup to display it in the "week" columns, using the approach I outlined in my previous post. (Of course, that is just one suggested approach. There are many other approaches you could take to display the values.)


Hope this helps.


Cheers,

Sajan.
 
Hi,

Got a few minutes between stuff... so let us look at a couple of additional "contour" formulas! (This is fun!)


I would suggest the following formula for calculating the "late peak" contour:

=(2^ROW($A$1:INDEX($A:$A,$B2))/(-2*(1-2^$B2)))*$A2

The approach I have used is to use powers of 2, which peaks rapidly as terms progress.

(Of course, you could use any geometric progression, such as cubes, etc.)


The formula for "early peak" contour would be the above values in reverse:

=(2^LARGE(ROW($A$1:INDEX($A:$A,$B2)),ROW($A$1:INDEX($A:$A,$B2)))/(-2*(1-2^$B2)))*$A2


(The second half of the formula [(-2*(1-2^$B2))] is just the sum of the first "n" terms of the geometric progression, with n being the duration value.)


(As before, cell A2 is the hours / effort value, and cell B2 is the duration value.)


Have fun!


Cheers,

Sajan.
 
Hello,

Following this forum I want to explain my problem and ask to you If you can help me.
I want to distribute a quantity of hours in a different months (the month are not 30 days, one month could be 20 days, other 15days...) the distribution is not a normal distribution is for example a early peak distribution. How I can do it? Waht shoul be the formula?
Thanks in advance,

Jordi
 
angryblackman said:
I'm trying to replicate (simply so I have a bit more control via Excel) some functionality from MS Project in Excel.


Background:

MS Project allows you to change how work is scheduled for a task using work contours

ie: if you have a task that will take 40 hours of effort and it's duration is 10 days by default Project will schedule a single resource 4 hrs a day for 10 days and that is a Flat Work Contour


You can assign a different Work Contour and Project will change the schedule effort to fit according the shape of the new Work Contour. (see .jpg)


What I'd like:

Is given a task effort and duration (ie: 100 hrs and 5 weeks), be able to have Excel distribute hours per week according to a work contour.


I think this a Math & Excel function problem, but I cannot figure it out. Any one have any ideas?


Thanks in advance

(i've attached a spreadsheet and image from MS Project).

The xlsx has a second tab that has a normdist formula which generates a bell curve distribution which I think is a step in the right direction


https://dl.dropbox.com/u/31649473/chandoo_work contour_help.jpg

https://dl.dropbox.com/u/31649473/chandoo_work contour_help.xlsx
Click to expand...


Can you solved this point?

Jordi
 
@Jordi
Hi!
Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.
Perhaps you'd want to read the first sticky forum contents so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
http://chandoo.org/forum/forums/new-users-please-start-here.14/
Regards!
PS: Please don't answer here at this thread.
 
Good evening SirJB7

You have fast keyboard skills:)

I was just about to inform Jordi of the error of his ways...;)


Jordi members may not wish to revisit this post as they have done so in the past and are unavailable to help so you post will not attract the help it could.
No matter how similar a question may be to another members post it is considered bad manners to take over their thread. Post your own with a clear title that will explain your problem and attract the right help.
 
Hi Sajan ,

I think it should be easier to develop a contour for any kind of distribution by using simple arithmetic.

In the uploaded file , the user decides the desired distribution by varying the values in row 2 ( D2 through O2 ) , using the chart as a visual aid.

If any other period is needed , additional changes to the ranges and the chart need to be done.

Narayan
 

Attachments

  • Example_Jordi.xlsx
    10.9 KB · Views: 77
Hi Narayank991,
How to calculate the ranges in the excel file taking into account only the period? changing the value in cell B2.
Does it mean, the ditribution for 12 month are in the excel file but what should be the distribution for 5 month? is possible to put some formula in the row2 ( D2 through O2 ) to distribute the hours in a short period with the early peak distribution?

Thanks!
 
Hi Narayan,
You are correct. The formulas can be greatly simplified.

(My original intent was to return the results as an array.)

Regards,
Sajan.
 
Jordi said:
Hi Narayank991,
How to calculate the ranges in the excel file taking into account only the period? changing the value in cell B2.
Does it mean, the ditribution for 12 month are in the excel file but what should be the distribution for 5 month? is possible to put some formula in the row2 ( D2 through O2 ) to distribute the hours in a short period with the early peak distribution?

Thanks!
Click to expand...

Hi Jordi ,

Can you check back tomorrow ? Today being Sunday , I'll try to find some time ; if not , positively tomorrow.

Narayan
 
Hi Jordi ,

Check your file now.

I have introduced DV drop-downs in B2 and C2.

Narayan
 

Attachments

  • Example_Jordi.xlsx
    11.6 KB · Views: 71
Jordi said:
Thanks for the answer I check the file.:D
Click to expand...
Hello Narayan,

I want to go far away...
I attach an example of what I want to do.
I have a 5 year vision month by month and I want according the starting date or month of the column F distribute the hours of the column E taking into account the number of month of the column H. Doe it mean if I change one of this paramethers the scenario changes but using the early peak distribution and the row 12 should be fill in with the hours per month according satrting month and quantity of months.

Regards,

Jordi
 

Attachments

  • jordi scenario.xlsx
    64.1 KB · Views: 61
You must log in or register to reply here.
Back
Top