What formula to use in Status column based on % Complete column?

Hi, I'm stumped...I've tried this formula multiple different ways and nothing seems to work.

Status column: Not Started, In Progress, or Complete

% Complete column: any number (column is set to %'s)

This is what I need: IF % Complete = 0, Status=Not Started, IF % Complete is greater than 0 and less than 100, Status=In Progress, IF % Complete = 100, Status=Complete.

This is the last example of what I tried=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row > 0, [% Complete]@row < 100), "In Progress", IF([% Complete]@row = 100, "Complete")))

Thanks in advance.

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @amber.lange & @John Hamilton

Just jumping in here to clarify that when you're looking at a % formatted type of column, the % is actually seen as a decimal in formulas. This means that instead of looking for 1 and 100, you'll want to look for 0.01 and 1.

Try John's same formula but with decimals instead:

=IF([% Complete]@row < 0.01, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))

You could also write this another way around:

=IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row = 0), "Not Started", "In Progress"))

Cheers,

Genevieve

All comments

John Hamilton

See if this might work for you.

=IF([% Complete]@row < 1, "Not Started", IF([% Complete]@row < 100, "In Progress", "Complete"))

amber.lange

@John Hamilton Thank you, I believe I already tried that, but did test again. The issue keeps coming up that when the % Complete is 100%, the status column will not update to Complete. If I remove the formatting of percentages from the % Complete column, the formulas seem to work, but we would prefer to keep the format as a percentage. Make sense?

John Hamilton

I set up some test data and it seemed to work for me using numbers only with no % sign.

Genevieve P.

Hi @amber.lange & @John Hamilton

Try John's same formula but with decimals instead:

=IF([% Complete]@row < 0.01, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))

You could also write this another way around:

=IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row = 0), "Not Started", "In Progress"))

Cheers,

Genevieve

John Hamilton

@Genevieve P. Thanks for the clarification. Always great to learn new "stuff"

amber.lange

@Genevieve P. Thank you! That worked perfectly and the explanation was so helpful.

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.