Answered by:
Need help on formula to compare task duration to project's overall baseline duration.

Question
-
Seems like this should be easy, so I must be missing something?
I need a formula to compare an individual task's start variance (I know how to get this as I need it) to the project's OVERALL BASELINE duration. I do not want to compare to the project's (current) Duration, and I do not want to compare to the individual task's duration - I can write formulas for each of those - but I need the overall project's basline Dur. I would think I should be able to get this from the Summary Task and cannot figure out how?
In other words, I have an overall project Baseline Duration of ~77d and a Duration of ~85d and I want to see how Task N's Start Var of 3d compares to the Project Baseline Duration of 77. In this example, I am looking for 3.90% variance from 3d/77d, not 3.53% which is 3d/85d.
Any help?
Thanks
Wednesday, July 18, 2012 2:24 PM
Answers
-
Sub StarVarPerc() Dim T As Task For Each T In ActiveProject.Tasks If Not (T Is Nothing) Then If T.Summary = False Then If T.StartVariance > 0 Or T.StartVariance < 0 Then T.Text1 = Format(T.StartVariance / ActiveProject.ProjectSummaryTask.BaselineDuration, "0.00%") End If End If End If Next T End Sub
Brian Kennemer - Project MVP
DeltaBahn Senior Architect
endlessly obsessing about Project Server…so that you don’t have to.
Blog | Twitter | LinkedIn- Edited by Brian Kennemer, MVP Thursday, July 19, 2012 4:20 AM
- Marked as answer by Mike Glen Sunday, August 26, 2012 8:06 PM
Thursday, July 19, 2012 4:19 AM
All replies
-
Joe --There is no easy way to accomplish this goal unless you are willing to write VBA code. When you write a formula, Microsoft Project calculates the formula for every task and summary task individually. There is no way in a formula to refer to a value from any other task, not even from the Project Summary Task (Row 0 or Task 0). Remember that formulas in Microsoft Project are similar to formulas in Excel, but they are not identical. Hope this helps.
Dale A. Howard [MVP]
VP of Educational Services
msProjectExperts
http://www.msprojectexperts.com
http://www.projectserverexperts.com
"We write the books on Project Server"Wednesday, July 18, 2012 2:41 PM -
Thanks Dave.
Guess I wasn't missing something after all. Doesn't seem all that odd a request - I'm surprised there's not an easier way.
Well, it's not critical, but if anyone is up to some VBA code, I'll take it.
Thanks all!
Wednesday, July 18, 2012 8:51 PM -
Dave --Although the request does not seem odd, I will agree, your request defies how formulas actually work in Microsoft Project. To explore the VBA angle, you might want to repost your question in the Project Customization and Programming user forum at:Hope this helps.
Dale A. Howard [MVP]
VP of Educational Services
msProjectExperts
http://www.msprojectexperts.com
http://www.projectserverexperts.com
"We write the books on Project Server"Wednesday, July 18, 2012 9:03 PM -
Sub StarVarPerc() Dim T As Task For Each T In ActiveProject.Tasks If Not (T Is Nothing) Then If T.Summary = False Then If T.StartVariance > 0 Or T.StartVariance < 0 Then T.Text1 = Format(T.StartVariance / ActiveProject.ProjectSummaryTask.BaselineDuration, "0.00%") End If End If End If Next T End Sub
Brian Kennemer - Project MVP
DeltaBahn Senior Architect
endlessly obsessing about Project Server…so that you don’t have to.
Blog | Twitter | LinkedIn- Edited by Brian Kennemer, MVP Thursday, July 19, 2012 4:20 AM
- Marked as answer by Mike Glen Sunday, August 26, 2012 8:06 PM
Thursday, July 19, 2012 4:19 AM -
That's Perfect! Thanks all.Thursday, July 19, 2012 5:16 PM