Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Making % Complete Column Accurately Reflect Work Done

edited 12/09/19 in Archived 2015 Posts
09/11/15 Edited 12/09/19



I am looking for a way to make the % complete on my parent rows reflect only work that takes effort, rather than processes that are just elapsed time.


For background, my group uses smartsheet for project management purposes, but we also use it for billing. Our parent rows in our project sheets are all billing categories, for example, we charge $x for "Data Collection" and $y for "Data Analysis," but each is a long process with many steps, some of which take effort/resources/man-hours, and some of which are just elapsed time. At set times (ie quarterly) we check the % complete of the parent rows for our projects, and bill our clients for the percent of the total that is complete. So if I have an ongoing task that takes many days but no effort, it contributes disproportionately for the % complete, so it may say we are 20% complete when we have really done 70% of the work.


I know this may not seem like a serious problem but it would help our smartsheet meet multiple important needs for our group.


The elapsed time feature has been helpful but obviously does not fix this issue. It would be great if there is some way to designate a row so that it will not count for the total % complete calculation. 


Any ideas?


  • Brett EvansBrett Evans ✭✭✭✭✭

    Build another column that is called %effort


    Assume for example Parent Task A with Child tasks X,Y and Z.  And asume that X is 50% of the effort, Y is 40% of the effort and Z is 10% of effort.


    Then the formula for %effort for X would be .5 * % Complete.  Y and Z would follow suit.  You have to use =Sum() on the parent row, Task A.


    Example linked.



  • JohnHinkleJohnHinkle ✭✭✭✭✭

    You could add an additional %effort column with this formula:




    This will show an average of child row percent complete. It wouldnt show a weighed average like the automatic percent complete parent row does, but it could be a good way to show the % of work done, only on items with a percent. 

  • edited 09/14/15

    Hi John- I'm new to Smartsheet. I tried your %effort foumula but I get an error message #DIVIDE BY ZERO

    My column headings are:

    Start Date

    Due Date

    Date Completed


    For two child tasks, Date Completed is blank.

    How do you suggest I structure the sheet to take advantage of =AVG(CILDREN()



  • JohnHinkleJohnHinkle ✭✭✭✭✭
    edited 09/14/15

    Lauren, if you do not have any values in the child row %effort column then you will get that error (because its trying to average but there are no numbers). Once you add percent values to those cells, the error will go away. You can also add 0% as a temporary number until you have the correct %. 

  • edited 09/14/15


    Excellent! There are 3 child tasks. One is 100% complete and the other two 0% complete.

    The parent row now shows .33333 instead of 33%. How do I convert decimal to percentage?

    Thanks, Lauren

  • JohnHinkleJohnHinkle ✭✭✭✭✭

    Click in the cell and press the Percent Format button on your toolbar (towards the bottom). You can also format the entire column by clicking on the column header (which will highlight the column) and press the Percente Format button

This discussion has been closed.