Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    If Formula with a Dropdown List

    ✭✭
    in Formulas and Functions

    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.

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭✭
      edited 12/30/20 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"))))

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭
      edited 12/30/20 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"))))

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      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.

      · Share on FacebookShare on Twitter
    • Employee

      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

      · Share on FacebookShare on Twitter
    • ✭✭
      Nic Larsen

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭
      Ben Goldblatt

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions