Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gant Chart/Programme Progress Monitoring
I am trying to create a programme progress monitor for the projects in my
work. All comments on this problem are welcome. I have a worksheet which monitors progress. it is based on the dates referred to in a contractor's construction programme/ gant chart. For simplicity, in my example, I will name the columns consecutively. Columns A and B have the start and end dates of the activities, respectively. I have further columns with weekdays() formalas etc. but these are not relevant to my problem. Column H is the contractors estimate of progress as a persentage and column I is the contractor's number of days - calculated as before for C and D. So I have something like this: A B C D Start End % days complete 01/01/07 10/01/07 100 10 01/02/07 10/03/07 100 38 10/03/07 14/05/07 90 28 14/05/07 25/07/07 5 7 I want to create a sheet two to monitor progress and specify how many weeks behind programme the project is running. Column A the the week commencing Column B the number of weeks remaining Column C the accumulative (running total) value of activity days Column D the contractors progress (days). Column E the number of weeks behind - matching the contractors progress with the value of the accumulative total and counting the number of weeks above. E.g D3 is the same as C2 (150) and the contracotr is therefore 1 week behind. A B C D E Week C. No. R Acc Prog Behind 01/01/07 35 105 80 -- 08/01/07 34 150 121 1.1 15/01/07 33 210 150 1.0 22/01/07 32 220 183 1.5 29/01/07 31 230 204 1.7 05/02/07 30 245 229 0.9 12/02/07 29 280 238 0.9 19/02/07 28 350 271 0.9 26/02/07 27 380 296 1.8 05/03/07 26 450 353 1.9 12/03/07 25 480 425 1.2 19/03/07 24 505 473 0.9 etc I'm stuck at trying to calculate Column C. I tried =SUMIF('Sheet1'!B2:B100, "=<A2", 'Sheet1'!D2:D100) But I always get a 0 value result. Column D is basucally the same only referring to 'Sheet1'!I2:I100 for the contractor's estimate of progress. I'm unsure how to calculate the number of weeks behind. Logically I am counting the number of cells 'up' on column C from the matching figure in the cell in column D. Kind Regards Dylan Scotland |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gant Chart/Programme Progress Monitoring
Try the following resources:
http://www.xl-logic.com/xl_files/charts/chart_gantt.zip or http://j-walk.com/ss/excel/files/gantt.exe Cheers Michael "dd" <dd.dd wrote in message ... I am trying to create a programme progress monitor for the projects in my work. All comments on this problem are welcome. I have a worksheet which monitors progress. it is based on the dates referred to in a contractor's construction programme/ gant chart. For simplicity, in my example, I will name the columns consecutively. Columns A and B have the start and end dates of the activities, respectively. I have further columns with weekdays() formalas etc. but these are not relevant to my problem. Column H is the contractors estimate of progress as a persentage and column I is the contractor's number of days - calculated as before for C and D. So I have something like this: A B C D Start End % days complete 01/01/07 10/01/07 100 10 01/02/07 10/03/07 100 38 10/03/07 14/05/07 90 28 14/05/07 25/07/07 5 7 I want to create a sheet two to monitor progress and specify how many weeks behind programme the project is running. Column A the the week commencing Column B the number of weeks remaining Column C the accumulative (running total) value of activity days Column D the contractors progress (days). Column E the number of weeks behind - matching the contractors progress with the value of the accumulative total and counting the number of weeks above. E.g D3 is the same as C2 (150) and the contracotr is therefore 1 week behind. A B C D E Week C. No. R Acc Prog Behind 01/01/07 35 105 80 -- 08/01/07 34 150 121 1.1 15/01/07 33 210 150 1.0 22/01/07 32 220 183 1.5 29/01/07 31 230 204 1.7 05/02/07 30 245 229 0.9 12/02/07 29 280 238 0.9 19/02/07 28 350 271 0.9 26/02/07 27 380 296 1.8 05/03/07 26 450 353 1.9 12/03/07 25 480 425 1.2 19/03/07 24 505 473 0.9 etc I'm stuck at trying to calculate Column C. I tried =SUMIF('Sheet1'!B2:B100, "=<A2", 'Sheet1'!D2:D100) But I always get a 0 value result. Column D is basucally the same only referring to 'Sheet1'!I2:I100 for the contractor's estimate of progress. I'm unsure how to calculate the number of weeks behind. Logically I am counting the number of cells 'up' on column C from the matching figure in the cell in column D. Kind Regards Dylan Scotland |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gant Chart/Programme Progress Monitoring
Dear Michael
I'm quite happy with the programme, or gantt chart that I have. The point is I am trying to set up equations to calculate progress based on the chart's start/ end dates and the contractor's reported estimate of completion. However; I have used the spreadsheet in gantt.exe for a new project I'm working on. So, thank you for sending me this link. Dylan Scotland "Michael" wrote in message ... Try the following resources: http://www.xl-logic.com/xl_files/charts/chart_gantt.zip or http://j-walk.com/ss/excel/files/gantt.exe Cheers Michael "dd" <dd.dd wrote in message ... I am trying to create a programme progress monitor for the projects in my work. All comments on this problem are welcome. I have a worksheet which monitors progress. it is based on the dates referred to in a contractor's construction programme/ gant chart. For simplicity, in my example, I will name the columns consecutively. Columns A and B have the start and end dates of the activities, respectively. I have further columns with weekdays() formalas etc. but these are not relevant to my problem. Column H is the contractors estimate of progress as a persentage and column I is the contractor's number of days - calculated as before for C and D. So I have something like this: A B C D Start End % days complete 01/01/07 10/01/07 100 10 01/02/07 10/03/07 100 38 10/03/07 14/05/07 90 28 14/05/07 25/07/07 5 7 I want to create a sheet two to monitor progress and specify how many weeks behind programme the project is running. Column A the the week commencing Column B the number of weeks remaining Column C the accumulative (running total) value of activity days Column D the contractors progress (days). Column E the number of weeks behind - matching the contractors progress with the value of the accumulative total and counting the number of weeks above. E.g D3 is the same as C2 (150) and the contracotr is therefore 1 week behind. A B C D E Week C. No. R Acc Prog Behind 01/01/07 35 105 80 -- 08/01/07 34 150 121 1.1 15/01/07 33 210 150 1.0 22/01/07 32 220 183 1.5 29/01/07 31 230 204 1.7 05/02/07 30 245 229 0.9 12/02/07 29 280 238 0.9 19/02/07 28 350 271 0.9 26/02/07 27 380 296 1.8 05/03/07 26 450 353 1.9 12/03/07 25 480 425 1.2 19/03/07 24 505 473 0.9 etc I'm stuck at trying to calculate Column C. I tried =SUMIF('Sheet1'!B2:B100, "=<A2", 'Sheet1'!D2:D100) But I always get a 0 value result. Column D is basucally the same only referring to 'Sheet1'!I2:I100 for the contractor's estimate of progress. I'm unsure how to calculate the number of weeks behind. Logically I am counting the number of cells 'up' on column C from the matching figure in the cell in column D. Kind Regards Dylan Scotland |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Gant and Line Chart | Charts and Charting in Excel | |||
Conditional format - gant chart | Excel Discussion (Misc queries) | |||
Excel to Gant Chart??? | Excel Discussion (Misc queries) | |||
Graph to create a Gant Chart | Excel Discussion (Misc queries) | |||
Free Excel Templates for monitoring Student progress in a Youth E. | Excel Discussion (Misc queries) |