How to I calculate percentage progress based on status?

Options

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!

Tags:

Answers

  • John_Foster
    John_Foster ✭✭✭✭✭✭
    Options

    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))))

    John

  • Sing C
    Sing C ✭✭✭✭✭✭
    Options

    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.

    Thanks,

    Sing

    Sing Chen

    Process Architect, Ceridian

    LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!