Website Upgrade Incoming - we're working on a new look (and speed!) standby while we deliver the project

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Planned % Complete - MS Project

6 replies [Last post]
Fri, 2009-07-31 03:31
Kayode Oni
User offline. Last seen 11 years 21 weeks ago. Offline
Joined: 10 May 2007
Posts: 15
Groups: Petro-Chem / Oil & Gas
Hi Guys,

A little query using the formula below to calculate planned % complete.
IIf([Baseline1 Start]>[Status Date],0,IIf([Baseline1 Finish]<=[Status Date],1,ProjDateDiff([Status Date],[Baseline1 Start],"Standard")/ProjDateDiff([Baseline1 Finish],[Baseline1 Start],"Standard")))

Using the average option for Roll Up summary tasks, gives the wrong calculation, especially when milestones are involved. (By the way, "Use formula" doesnt give an accurate projection either)
Does anyone know how to do weighted average using this formula?

Many thanks.
Top

Replies

Sun, 2009-08-02 10:25
#1
Trevor Rabey
User offline. Last seen 2 years 48 weeks ago. Offline
Joined: 29 Nov 2005
Posts: 530
Groups: None
http://groups.google.com.au/group/microsoft.public.project/browse_thread...
Top
Sun, 2009-08-02 10:24
#2
Trevor Rabey
User offline. Last seen 2 years 48 weeks ago. Offline
Joined: 29 Nov 2005
Posts: 530
Groups: None
http://forums.techarena.in/microsoft-project/1198439.htm
Top
Sun, 2009-08-02 07:31
#3
Kayode Oni
User offline. Last seen 11 years 21 weeks ago. Offline
Joined: 10 May 2007
Posts: 15
Groups: Petro-Chem / Oil & Gas
I accept, not enough detail in the example.
All baseline Dates.
Task 1 Milestone - Friday 31/07/09
Task 2 (FS) Task 1 - 5 Days - Monday 03/08/09 to Fri 07/08/09
Task 3 (FS) Task 2 - 10 Days - Monday 10/08/09 to Fri 21/08/09.
Overall duration is 16days (including milestone start date.)
Current status date is Fri 31.07.09 - End of play.

With Stats above, overall planned % complete (Baseline) as at 31.07.09 will be calculated as 33%, simple average.
Using the same stats as actual start & finish dates, automatically calculated by MSP, overall % complete will be 0% as at 31/07/09, simply because task 1 is a milestone - weighted average.
Hope my question is slightly clearer.


Top
Sat, 2009-08-01 20:33
#4
Trevor Rabey
User offline. Last seen 2 years 48 weeks ago. Offline
Joined: 29 Nov 2005
Posts: 530
Groups: None
Not the case?
If you say that the Summary % Complete in your example is not supposed to be 33%, what do you think it should be?

There is not enough information in your example.
What is the baseline start, finish and overall duration of the summary (due to the tasks predecessors, lag, dates etc)?
What is the baseline start, finish and overall duration of the tasks?
What is the current start, finish and overall duration of the summary (due to the tasks predecessors, lag, dates etc)?
What is the current start, finish and overall duration of the tasks?
Where is the status date?
Top
Sat, 2009-08-01 07:07
#5
Kayode Oni
User offline. Last seen 11 years 21 weeks ago. Offline
Joined: 10 May 2007
Posts: 15
Groups: Petro-Chem / Oil & Gas
Hi Trevor, thanks for the response.

May be I haven’t posed the question properly. The formula works well. Absolutely nothing wrong it. The issue I was referring to in my original post arises when attempting to get an overall Planned % complete for a project.
Using the average option for summary tasks, MSP takes an average of all planned %s. Sometimes, this doesnt give a true reflection. Weighted average(Function of duration etc) is required.
Example - Summary task with 3 subtasks
Task 1 - Milestone - 100%(Planned % Complete)
Task 2 - 5 days - 0% (planned % complete)
Task 3 - 10 days - 0% (planned % complete)
MSP will calculate Overall planned % complete as 33% -clearly not the case.
I guess solution the use of a weighted average column, just like MSP calculates normal roll up summaries. How?????
Top
Sat, 2009-08-01 04:34
#6
Trevor Rabey
User offline. Last seen 2 years 48 weeks ago. Offline
Joined: 29 Nov 2005
Posts: 530
Groups: None
I don’t agree. You say that the Summary calculation is not correct with Use Formula, but you don’t say why.
But of course it is correct because it has a Baseline1 Start and the calculation is just the ratio of the duration from the Baseline1 Start to the status date divided by the total Baseline1 Duration.

There are so many things that can go wrong with your formula that it is not worth doing unless you are prepared to account for other variables. For example, you use the Standard Calendar for all of the ProjDateDiffs, but the Tasks and Summaries might all use different calendars.

This is a lot of trouble to go to just to attempt to replicate something which is already built into the functionality of MSP.
Top