% Formula based on another column


Hello - looking for a formula for reading another column drop down selection (Not started, In progress, Blocked, Complete). So when drop down is selected percentage column changes base don that value.

Not Started = 0%

In Progress = 50%

Blocked = 0%

Complete = 100%

thank you in advance


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Morris R,

    You can do this with a nested IF (make sure your Percentage column is set to % from the tool bar):

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

    This should give the relevant percentage for the status types outlined and otherwise be blank.

    Hope this helps, but if you've any problems/questions then just let us know!

  • PCG Sam Harwart
    PCG Sam Harwart ✭✭✭✭

    Hi @Morris R ,

    You can use a nested IF statement to do that. I'd recommend making it a column formula as well.

    =IF([Status Dropdown]@row = "Complete", 1, IF([Status Dropdown]@row = "In Progress", 0.5, IF([Status Dropdown]@row = "Blocked", 0, IF([Status Dropdown]@row = "Not Started", 0, "--"))))


    primeconsulting.com | Smartsheet's 2023 Partner of the Year for North America
    Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call!

  • Morris R
    Morris R ✭✭✭✭

    Awesome ! thank you - worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!