Gant Chart/Programme Progress Monitoring

[dd]

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'!D2100)
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

 

[Michael]

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]

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

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