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.
Best Answer

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"))))
Answers

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"))))

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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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 columnlevel formula by rightclicking the formula cell and selecting "Convert to Column Formula".
I hope this helps!
Thanks,
Ben

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.

Can we do another formula or calculation based ok the % that we just obtained, which are in a drop down list also
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!