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 column-level formula by right-clicking 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
Check out the Formula Handbook template!
