Prev: HTML Maps
Next: importing schedule into outlook calendar
From: Lauren on 26 Mar 2010 15:31
All,

I would appreciate some help with the following formula.

Goal: For each task and roll up task, understand what the % complete would
be if every task is on schedule. Think of it as a forecasted % complete in
relation to todays date. The roll up would be weighted similar to the actual
% complete, such that longer tasks are weighted more heavily than shorter
tasks at each level.

If I was using project this is how I was trying to express.

if([Finish]=<[Current Date], 1, if([Start]=< [Current Date], ([Current
Date]-[start])/[Duration],0) * 100 + "%"

This has not worked for me can anyone suggest another approach.

Thanks!
Lauren


From: Andrew Lavinsky on 26 Mar 2010 16:33
I didn't test extensively, but this should more or less work:

IIf([Status Date]=ProjDateValue('NA'),"No Status Date Set",IIf([Baseline
Start]=ProjDateValue('NA'),"No Baseline Set",IIf([Status Date]<[Baseline
Start],"Scheduled in Future",IIf([Status Date]>[Baseline Finish],"100%",100*ProjDateDiff([Baseline
Start],[Status Date])/[Baseline Duration] & "%"))))

Note that this uses the Status Date rather than the Current Date - which
is probably a slightly better solution. To set the Status Date, go to Project
> Information.

- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

> All,
>
> I would appreciate some help with the following formula.
>
> Goal: For each task and roll up task, understand what the % complete
> would be if every task is on schedule. Think of it as a forecasted %
> complete in relation to todays date. The roll up would be weighted
> similar to the actual % complete, such that longer tasks are weighted
> more heavily than shorter tasks at each level.
>
> If I was using project this is how I was trying to express.
>
> if([Finish]=<[Current Date], 1, if([Start]=< [Current Date], ([Current
> Date]-[start])/[Duration],0) * 100 + "%"
>
> This has not worked for me can anyone suggest another approach.
>
> Thanks!
> Lauren


From: Andrew Lavinsky on 26 Mar 2010 16:43
Sorry - I missed the rollup instructions....

To rollup the way you would like it with weighted tasks, it might be a bit
more difficult. Here's an attempt:

1) Create a Duration field that rolls up as the SUM: IIf([Status Date]>[Baseline
Finish],1,ProjDateDiff([Baseline Start],[Status Date])/[Baseline Duration]
& "%")*[Baseline Duration]. This will give you the sum of the subtask Actual
Durations.

2) Create a second Duration field that equals [Baseline Duration]. Roll
that up the summary level as the SUM as well.

3) Create a third Text field which divides Duration1/Duration2

The trick is that there's no built-in field which sums up the total Actual
Duration or the total Duration - although they could perhaps be derived with
some work. I am not sure how well this will roll up from second tier summary
tasks to first tier summary tasks, so you may need to tweak it.


- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

> All,
>
> I would appreciate some help with the following formula.
>
> Goal: For each task and roll up task, understand what the % complete
> would be if every task is on schedule. Think of it as a forecasted %
> complete in relation to todays date. The roll up would be weighted
> similar to the actual % complete, such that longer tasks are weighted
> more heavily than shorter tasks at each level.
>
> If I was using project this is how I was trying to express.
>
> if([Finish]=<[Current Date], 1, if([Start]=< [Current Date], ([Current
> Date]-[start])/[Duration],0) * 100 + "%"
>
> This has not worked for me can anyone suggest another approach.
>
> Thanks!
> Lauren


From: Jim Aksel on 27 Mar 2010 01:01
I have several options for this posted on my blog, check link below.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com



"Lauren" wrote:

> All,
>
> I would appreciate some help with the following formula.
>
> Goal: For each task and roll up task, understand what the % complete would
> be if every task is on schedule. Think of it as a forecasted % complete in
> relation to todays date. The roll up would be weighted similar to the actual
> % complete, such that longer tasks are weighted more heavily than shorter
> tasks at each level.
>
> If I was using project this is how I was trying to express.
>
> if([Finish]=<[Current Date], 1, if([Start]=< [Current Date], ([Current
> Date]-[start])/[Duration],0) * 100 + "%"
>
> This has not worked for me can anyone suggest another approach.
>
> Thanks!
> Lauren
>
>
 | 
Pages: 1
Prev: HTML Maps
Next: importing schedule into outlook calendar