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.

Percent Complete - formulas


I have tried to make my % Complete column a formula which apparently is not allowed. I have 3 columns consisting of 1) Total story points; 2) Remaining story points and 3) % Complete. I want to use the following formula in the % Complete column ((Total Story Points - Remaing Story Points) / Total Story Points). Has anyone successfully tried doing something like this before?


  • Allie

    It's definitely possible to build that formula in Smartsheet, but not in the % Complete column as long as it's configured in the Project Settings. You could remove the column from the Project Settings (right-click a column, select Project Settings, then change the "% Complete column" to none) and then input the formula to it, but in this case the Gantt bars won't fill in with their percentage complete.


    You could also input the formula to a different text/number column. The formula would look something like this for row 2: =([Total Story Points]2 - [Remaining Story Points]2)/[Total Story Points]2


    Hope this helps!

  • JamesR
    JamesR ✭✭✭✭✭✭

    To add to Allies comment, once you have entered the formulae into the new column then in Project settings point the % complete at the new column.  Format the column as Percentage.

  • Samantha Colborn
    edited 08/05/16

    I am running into the same issue. I have a sheet that is syncing the updates from Jira. I had performed an initial sync and calculated the % complete based on the status code from Jira. However, to get the rollups and Gantt chart to show correctly, I turned on the dependancies and % Complete features in Smartsheet and lost my formula. I haven't been able to find an automated way to do this. 

  • Brad Jones
    Brad Jones ✭✭✭✭✭✭

    CRAP!!  I just ran into this same issue.    OUCH OUCH OUCH.  Now I have to reformat several workspaces just so that I can work around this limitation, re-write my user manual, rework my training videos, etc.  This is gonna cost me some serious time Cry

  • Scott Allen

    It seems like this should be an easy solution if you have nested rows under another in order to get % Complete to total those rows and now you next all under a new TOTAL Project % complete Row. But alas, as noted here it doesn't work.


    and my fear of using a created calculation is that if we add rows and forget it's a very manual process to update the totals etc? 


    I would hope this could be addressed by smart sheet for an easy resolution? Though based on the age of this thread, I guess not yet....

  • David Mather

    I'd like to add my vote to getting this feature enabled. I set up a sheet with a series of check-boxes in columns to populate a % Complete column. It works ok but as previously noted when users add rows I am going to have to go in and re-input formula.  I could put those check-boxes in nested rows but it in this case it makes the sheet four times longer than it needs to be.



  • Trevor Textor

    Please add my vote as well!

  • swh@dr
    swh@dr ✭✭

    I am sure I am doing something wrong. When I do this, my formula disappears and I am told I can't type in this field.

    Any idea what I am doing wrong?


This discussion has been closed.