"=IF" statement not generating correct response all of the time

Hi Everyone!

I am trying to get my status column to change based upon what the number is the [% Complete) column is. Here is what I would like the desired results to be:

0= "Not Started"; 100="Complete"; and anything else from 1-99 would be "In Progress"

Here is the formula that I have tried, but it's not catching that 100% should be "Complete".  

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

Here is screenshot of the error.


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are very close. Smartsheet reads percentages as a decimal. so 100% = 1, 50% = 0.5, etc. All we need to do is adjust accordingly.


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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You are very close. Smartsheet reads percentages as a decimal. so 100% = 1, 50% = 0.5, etc. All we need to do is adjust accordingly.


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

  • OMG, I wish I had asked sooner, I spent 3 days trying to figure this out on my own. Thank you soooo much!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!