If Formula with a Dropdown List

I have a column titled Status that includes the following drop down selections: Not Started, In Progress, On Hold and Complete. I want to write a formula that based on the drop down selected, another row would populate the following automatically:

Not Started = 0%, In Progress = 50%, Completed = 100% and On Hold = ON HOLD. I have tried a number of formula calibrations and keep getting UNPARSABLE in my cell.

Looking for suggestions.

Find more posts tagged with

Formulas

Accepted answers

Nic Larsen

Try: =IF([Status]@row = "Completed", 1, IF([Status]@row = "In Progress", 0.5, IF([Status]@row = "Not Started", 0, IF([Status]@row = "On Hold", "ON HOLD"))))

All comments

Nic Larsen

Try: =IF([Status]@row = "Completed", 1, IF([Status]@row = "In Progress", 0.5, IF([Status]@row = "Not Started", 0, IF([Status]@row = "On Hold", "ON HOLD"))))

Mark Cronk

Hi @Paula Seward ,

Sounds straight forward. Create your [% Complete] column as text/ number and formated as %. In it place the formula:

=IF(Status@row="Complete", 1, IF(Status@row="In Process", .5, =IF(Status@row="On Hold", "On Hold", 0)))

Your Status column needs to be single select and restricted to the list.

Work?

Mark

Ben Goldblatt

Hi @Paula Seward,

Looks like everyone is on the same page here though there are variants to the formula that can be used to accomplish the same results. If the logic break down is:

Not Started = 0%

In Progress = 50%

Completed = 100%

On Hold = On Hold

I would use this for my formula:

=IF(Status@row = "Complete", 1, IF(Status@row = "In Progress", 0.5, IF(Status@row = "Not Started", 0, "On Hold")))

The order of the Status values doesn't matter in this type of formula and you can take advantage of the value_if_false argument of the IF Function for the "On Hold" result, basically saying if none of these other conditions are met, populate the value of "On Hold". Once your formula is set, you can convert it into a column-level formula by right-clicking the formula cell and selecting "Convert to Column Formula".

Screen Shot 2020-12-30 at 2.22.33 PM.png

I hope this helps!

Thanks,

Ben

Paula Seward

https://community.smartsheet.com/discussion/comment/273400#Comment_273400

Thanks everyone!! Nic's response worked like a charm. It did not like adding the percentage sign in my original formula but doing individual numbers and changing to percentage for the column properties did the trick! Thanks again. Super helpful.

Otman

https://community.smartsheet.com/discussion/comment/273404#Comment_273404

Can we do another formula or calculation based ok the % that we just obtained, which are in a drop down list also

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.