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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.