How to I calculate percentage progress based on status?

Fei Teng

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

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

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