IF and AND for driving progress bar Symbol

For progress column, I want to use the symbol column with bar option (Empty, Quarter, Half, Three Quarter, Full) and it should be driven by task % Complete. if % Complete Value is 0% then progress should be Empty, if complete value is between 0 - 35% then progress should be Quarter, if value is between 36-65% then progress should be Half, if value is between 66-90% then progress should be Three Quarter, if value is above 91% then progress should show full.
I have tried both the below options in progress, but Smartsheet stops calculation post quarter. any help would be appreciated.

Option 1: (Not working after Quarter)

=IF([% Complete]@row = 0, "", IF(AND([% Complete]@row > 0, [% Complete]@row <= 35), "Quarter", IF(AND([% Complete]@row >= 36, [% Complete]@row <= 65), "Half", IF(AND([% Complete]@row >= 66, [% Complete]@row <= 90), "Three Quarter", IF([% Complete]@row >= 91, "Full")))))

Option 2:

(Not working after Quarter)

=IF([% Complete]@row = 0, "Empty",IF([% Complete]@row >= 91, "Full",IF([% Complete]@row >= 66, "Three Quarter",IF([% Complete]@row >= 36, "Half",IF([% Complete]@row > 0, "Quarter")))))

Find more posts tagged with

Formulas

IF(AND

symbols

Accepted answers

Jgorsich

Percentages are between 0 and 1, 50% = 0.5.

Additionally, right now your formula doesn't account for a value like 35.5% at all. If() statements stop at the first true result, so you can always presume every test before is false and rewrite like this:

=IF([% Complete]@row = 0, "", IF([% Complete]@row <= 0.35, "Quarter", IF( [% Complete]@row <= 0.65, "Half", IF([% Complete]@row <= 0.90, "Three Quarter", "Full"))))

All comments

Jgorsich

Percentages are between 0 and 1, 50% = 0.5.

=IF([% Complete]@row = 0, "", IF([% Complete]@row <= 0.35, "Quarter", IF( [% Complete]@row <= 0.65, "Half", IF([% Complete]@row <= 0.90, "Three Quarter", "Full"))))

Shashank

Perfect, it worked well. Thanks so much Jgorsich.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.