What formula to use in Status column based on % Complete column?

Hi, I'm stumped...I've tried this formula multiple different ways and nothing seems to work.

Status column: Not Started, In Progress, or Complete

% Complete column: any number (column is set to %'s)

This is what I need: IF % Complete = 0, Status=Not Started, IF % Complete is greater than 0 and less than 100, Status=In Progress, IF % Complete = 100, Status=Complete.

This is the last example of what I tried=IF([% Complete]@row = 0, "Not Started", IF(AND([% Complete]@row > 0, [% Complete]@row < 100), "In Progress", IF([% Complete]@row = 100, "Complete")))

Thanks in advance.


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @amber.lange & @John Hamilton

    Just jumping in here to clarify that when you're looking at a % formatted type of column, the % is actually seen as a decimal in formulas. This means that instead of looking for 1 and 100, you'll want to look for 0.01 and 1.

    Try John's same formula but with decimals instead:

    =IF([% Complete]@row < 0.01, "Not Started", IF([% Complete]@row < 1, "In Progress", "Complete"))

    You could also write this another way around:

    =IF([% Complete]@row >= 1, "Complete", IF(OR([% Complete]@row = "", [% Complete]@row = 0), "Not Started", "In Progress"))




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!