How to I calculate percentage progress based on status?


Hello Smartsheet Community,

I appreciate your support on calculating "% of complete" based on "Status" value. I have some predefined values for "Status" column and need to connect the "Status" to "% of Complete". For example:

When Status="Backlog" --> % Complete=0%

When Status="In Evaluation" --> % Complete = 25%

When Status="In Draft" --> % Complete=50%

When Status="In Approval" --> % Complete= 75%

When Staus= "Completed" --> % Complete=100%

Appreciate your help on formula for the "% Complete" column.

Thank you!



  • John_Foster
    John_Foster ✭✭✭✭✭✭

    Hi @Fei Teng,

    You could try using the formula below, you may need to tweak the column names, but see how you get on.

    IF([Status]@row="Completed",1,IF([Status]@row="In Approval",0.75,IF([Status]@row="In Draft",0.5,IF([Status]@row="In Evaluation",0.25,0))))


  • Sing C
    Sing C ✭✭✭✭✭✭

    Hi @Fei Teng,

    This should get you started on the formula:

    =IF(Status@row = "Backlog", 0, IF(Status@row = "In Evaluation", 0.25))

    You can add the rest of the elements (In Draft, In Approval, Completed) by adding additional IF clauses to complete the formula.

    Let me know if that works for you! Have a great day.



    Sing Chen

    Process Architect, Ceridian


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!