none
% compete Column shows more than 100% RRS feed

  • Question

  • Hellow there..

    This is my first question and I hope to get it answered by experts here.

    In MS project pro, I'm using this formela to calculte the Expected % compete column:

    IIf([Scheduled Duration]=0,IIf([Scheduled Finish]<=[Status Date],1,0),[Number11]/[Number10])*100

    It was working just fine.. but all of sudden I noticed that this column for some Tasks has value of 120% . how is that possible? Also sometimes it shows an Error value next to overall Project sumary task untill I inter any value in % complete of any task to clear that error value.

    The columns I disply:

    • Tak.
    • Duration.
    • % Complete.
    • Expected % complete.
    
    
    
    
    
    
    
    I apreciate your help.



    • Edited by sAlzubaidi Monday, December 5, 2016 10:53 AM
    Monday, December 5, 2016 10:46 AM

Answers

  • sAlzubaidi,

    In my quick read of this thread I didn't notice that anyone pointed out that values in the Duration field are stored internally in minutes regardless of how they are displayed in the Duration field. If the user is not aware of that, formulas using the duration value can often give very strange results.

    John

    • Marked as answer by sAlzubaidi Wednesday, December 7, 2016 12:42 PM
    Monday, December 5, 2016 2:20 PM
  • I guess we need a few questions answered:

    : what is in the column named [Number11]?

    : what is in the column named [Number 10]?

    This is needed because the numbers in those fields are what drives the computation (after all the IF statements pass True) from the formula you give.

    I suspect that you may believe that just showing the columns on the view define the computation and the values of the NumberXX fields. That is not the case.  The numbers you put in those fields (via displaying them in a column and putting in by hand or by doing some sort of computation) are the numbers your formulae will use.  Project views (rows and columns) do not work like Excel (other than navigating around). Look at the custom field definitions for these Number fields.


    --rms www.rmschneider.com


    • Edited by Rob Schneider Monday, December 5, 2016 11:30 AM
    • Marked as answer by sAlzubaidi Wednesday, December 7, 2016 12:43 PM
    Monday, December 5, 2016 11:24 AM
  • So, only you can really assess, why when you do the computation [Expected Hours till now] divided by [Hours within Duration] you get the wrong answer?  What is wrong with the numbers in these fields? Both these parameters are controlled by you as they are clearly not standard Project fields.   

    --rms www.rmschneider.com

    • Marked as answer by sAlzubaidi Monday, December 5, 2016 12:24 PM
    Monday, December 5, 2016 11:57 AM
  • Also good to explain what it is you are trying to do.  I suspect that you don't need to (or should be) defining special formulae for this sort of thing, and instead use basic functionality and resources available in Project.

    Finally, in your reply above, you refer to "Project View named Duration" and "Project view named % complete". Views are not fields and visa versa.  While you might have Views with these names, you probably are referring to *fields* named these things.  Re-read how these fields in Project are created and used, especially % complete. 


    --rms www.rmschneider.com



    • Edited by Rob Schneider Monday, December 5, 2016 12:12 PM
    • Marked as answer by sAlzubaidi Wednesday, December 7, 2016 12:43 PM
    Monday, December 5, 2016 12:11 PM

All replies

  • I guess we need a few questions answered:

    : what is in the column named [Number11]?

    : what is in the column named [Number 10]?

    This is needed because the numbers in those fields are what drives the computation (after all the IF statements pass True) from the formula you give.

    I suspect that you may believe that just showing the columns on the view define the computation and the values of the NumberXX fields. That is not the case.  The numbers you put in those fields (via displaying them in a column and putting in by hand or by doing some sort of computation) are the numbers your formulae will use.  Project views (rows and columns) do not work like Excel (other than navigating around). Look at the custom field definitions for these Number fields.


    --rms www.rmschneider.com


    • Edited by Rob Schneider Monday, December 5, 2016 11:30 AM
    • Marked as answer by sAlzubaidi Wednesday, December 7, 2016 12:43 PM
    Monday, December 5, 2016 11:24 AM
  • Thanks rob for the swift reply..

    The custom field definitions for:

    1. column number 10 : Hours within duration. (Project view named Duration)
    2. column number 11 : Expected hours till now. (Project view named % complete)

    Hope this give a clear picture.

    
    
    Monday, December 5, 2016 11:54 AM
  • So, only you can really assess, why when you do the computation [Expected Hours till now] divided by [Hours within Duration] you get the wrong answer?  What is wrong with the numbers in these fields? Both these parameters are controlled by you as they are clearly not standard Project fields.   

    --rms www.rmschneider.com

    • Marked as answer by sAlzubaidi Monday, December 5, 2016 12:24 PM
    Monday, December 5, 2016 11:57 AM
  • Also good to explain what it is you are trying to do.  I suspect that you don't need to (or should be) defining special formulae for this sort of thing, and instead use basic functionality and resources available in Project.

    Finally, in your reply above, you refer to "Project View named Duration" and "Project view named % complete". Views are not fields and visa versa.  While you might have Views with these names, you probably are referring to *fields* named these things.  Re-read how these fields in Project are created and used, especially % complete. 


    --rms www.rmschneider.com



    • Edited by Rob Schneider Monday, December 5, 2016 12:12 PM
    • Marked as answer by sAlzubaidi Wednesday, December 7, 2016 12:43 PM
    Monday, December 5, 2016 12:11 PM
  • you're right..

    well, At least now I'm sure nothing wrong with my formela, and it all depends on the values entered in these fileds.

    I was hoping to be able to attached a snopshot showing some of thses values, but unfortionately my account needs to be verfied.

    but amyway thanks for yout help and support.

    I will mark your last reply as the ANSWER!

    Monday, December 5, 2016 12:23 PM
  • Also good to explain what it is you are trying to do.  I suspect that you don't need to (or should be) defining special formulae for this sort of thing, and instead use basic functionality and resources available in Project.

    Finally, in your reply above, you refer to "Project View named Duration" and "Project view named % complete". Views are not fields and visa versa.  While you might have Views with these names, you probably are referring to *fields* named these things.  Re-read how these fields in Project are created and used, especially % complete. 


    --rms www.rmschneider.com




    

    Ok, I simply want to show work completed as percentage against what is expeted/planned to be complete.

    and yes, I got the idea of views and fileds.

    So my views should be as follow:

    Task name <> Duration <> % complete <> Expected % complete <> Start date <> Finish Date

    So when I enter 20% as value in "% complete" column, it automaticlly calculates the value of "Expected % complete " column.

    If I dnot need to defining a special formulae for this , I would really appreciate telling me how to do it directly!

    If you can add more based in this reply, otherwise you already did just fine and I thank you for that.

    

    • Edited by sAlzubaidi Monday, December 5, 2016 12:41 PM
    Monday, December 5, 2016 12:40 PM
  • They are not views. They are "fields" or "columns" (same thing, two acceptable names). Several fields make a table. Some fields are built in and are either calculated by MSP or input by you. They can't be calculated by a function of the custom fields. Others fields can be custom fields with formulae in 'em. All of the ones you have shown here are built-in fields, except Expected % complete which could have your formula in it. Are you really dividing Number10 (duration) by Numer11 (% complete)? If so, because % complete = actual duration/duration, this should yield duration^2/actual duration. I'm lost at this point.
    Monday, December 5, 2016 1:03 PM
  • sAlzubaidi,

    In my quick read of this thread I didn't notice that anyone pointed out that values in the Duration field are stored internally in minutes regardless of how they are displayed in the Duration field. If the user is not aware of that, formulas using the duration value can often give very strange results.

    John

    • Marked as answer by sAlzubaidi Wednesday, December 7, 2016 12:42 PM
    Monday, December 5, 2016 2:20 PM
  • They are not views. They are "fields" or "columns" (same thing, two acceptable names). Several fields make a table. Some fields are built in and are either calculated by MSP or input by you. They can't be calculated by a function of the custom fields. Others fields can be custom fields with formulae in 'em. All of the ones you have shown here are built-in fields, except Expected % complete which could have your formula in it. Are you really dividing Number10 (duration) by Numer11 (% complete)? If so, because % complete = actual duration/duration, this should yield duration^2/actual duration. I'm lost at this point.
    
    

    Thank you Trevor for this informative reply!

    for more clearification, Let me rewrite the formula in a clearer way:

    IIF([Scheduled Duration]=0,IIf([Scheduled Finish]<=[Satus Date],1,0),[Expected Till Now]/[Hours Within Duration])*100

    And Note that this formula was working just fine and yield the result I need. The trigger for my question was when values for this custom field "Expected % complete" start showing values more than 100% for some tasks, Eg: 120%.

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    • Edited by sAlzubaidi Tuesday, December 6, 2016 7:39 AM
    Tuesday, December 6, 2016 7:38 AM
  • sAlzubaidi,

    In my quick read of this thread I didn't notice that anyone pointed out that values in the Duration field are stored internally in minutes regardless of how they are displayed in the Duration field. If the user is not aware of that, formulas using the duration value can often give very strange results.

    John


    John, Thanks for the heads up ... this is interesting, I' didnt know that!
    Tuesday, December 6, 2016 7:42 AM
  • sAlzubaidi,

    You're welcome and thanks for the feedback.

    John

    Tuesday, December 6, 2016 4:03 PM