none
Formula Error for Finish Variance RRS feed

  • Question

  • Greetings

    I am trying to create stoplight indicators to provide visual feedback on the status of assignments relative to the various baselines we use.  Our criteria for variance is:

    Green:  less than/equal to 0%

    Yellow: >0 and , 5%

    Red: >5%

    We will need to generate this for several different Baselines so it will be replicated with the basleine field changed for each iteration.

    To generate the value, I've used the following:

    Entity: Project

    Type: Number

    Formula: ( ([Baseline1 Finish] - [Finish]) / [Baseline1 Duration]) * 100

    When I save the Enterprise CUstom Field in PWA and then open the client, open a project, and insert the column, the value is #ERROR.

    Does anyone see anything I"m doing incorrectly?  I was able to do this in previous versions of Project, but cant' seem to get it to work here.

    Thanks,

    Ryan

    Wednesday, February 20, 2013 8:34 PM

All replies

  • RyanDenver --
     
    Do you live in Denver?  I used to live in Denver, about 2 miles from DU.
     
    Anyway, I believe your formula is not going to yield what you hope for.  As I read the formula, you are trying to determine the percentage of Duration slippage, based on a certain Baseline.  Correct?  If so, your formula should be as follows:
     
    (([Duration] - [Baseline Duration]) / [Baseline Duration]) * 100
     
    Using the Baseline Finish and Finish fields will not work at all if the project has slipped, yielding no meaningful data that I can see.  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, February 20, 2013 9:08 PM
    Moderator
  • Dale

    I do live in Denver.  I think we met and spoke a few times at the MPUG that Warren hosted and the PMI meetings.

    Thanks for the guidance.  The values in the fields I was using worked when I mocked it up in excel, but like I guessed, it didn't translate to project cleanly. 

    The goal is to find out as a % of the baseline "X" duration, what the actual over/under variance is.   I also need to do the same with work variance.

    I'll give those a try and see if it yields the expected results.

    Thanks again for your help.

    Ryan

    Wednesday, February 20, 2013 9:20 PM
  • Dale

    I tried that formula, but it's really providing a duration variance rather than a slippage variance.  Do I need to create an interim field for calculation of the finish to baseline 1 finish variance and then do further calculations in a secondary field?

    Here is some sample data I was able to get to work in Excel:

    Finish: 6/3/14

    Baseline 1 Finish: 7/20/13

    Baseline 1 Duration: 287d

    Variance: 308d  ([Finish]-[Baseline1 Finish])

    Variance %: 107 ([Variance] / [Baseline1 Duration])

    Thanks

    Ryan

    • Edited by RyanDenver Wednesday, February 20, 2013 10:20 PM
    Wednesday, February 20, 2013 9:31 PM
  • Ryan --
     
    I do not understand how you can calculate a percentage of slippage variance.  Percentage of what?  Calculating slippage as a percentage of the Baseline Duration does not make any sense.  A task has a Baseline Duration of 5 days, but the Finish date of the task has slipped 20 days.  So, what percentage would you calculate for that situation?  20/5 = 400%?  Does that make any sense?  It makes no sense to me.
     
    If you want to show how badly tasks are slipping, create a custom field using the following formula:
     
    ProjDateDiff([Baseline1 Finish],[Finish]) / [Minutes Per Day]
     
    That will give you a duration value measured in days, showing how late each task is against the Baseline1 set of baselines.  Then create graphical indicator criteria based on the number of days each task is late.
     
    Again, I want to stress to you that I think your percentage of slippage idea does not make sense.  But then again, that�??s just me.  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, February 20, 2013 10:24 PM
    Moderator
  • Dale

    Thanks again for your help.

    I will try that formula as I think that indicator is useful as well. 

    However, what my mgmt team is looking for is the slippage of completion date vs the entire project duration as a %.  In essence, they want to be able to say on avg, over the last X months, our projects have been on time, x% late/early, etc.  The same for work that projects have been X% over/under the original esitmate on work hours.

    There are other metrics we can/do use internally on projects, but from a mgmt perspective, this is the one set that they are focusing on right now. 

    If you have any thoughts on how to do this outside of excel, please let me know.

    Thanks,

    Ryan

    Thursday, February 21, 2013 3:19 PM
  • Ryan --
     
    Using a formula in Microsoft Project, I do not believe you will be able to calculate what your management wants.  Either that, or convince them that they need to use a formula that calculates the percentage of Duration variance.  Now I fully understand why you wrote the original formula the way you did.  I think Excel might provide a better avenue for doing this calculation.
     
    By the way, I forgot to respond to your comment that we probably met at an MPUG meeting in Denver.  Yes, I attended the Front Range MPUG meetings as often as I could, given the fact that my job has plenty of business travel.  Perhaps we will meet up again some day!  :)
     
    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"

    Thursday, February 21, 2013 3:44 PM
    Moderator