Problems using IF formula to calculate status

Miki Jeffers

Hello,

First post for a new Smartsheet user.  I'm trying to do an status formula.  If my % Complete column is 0% then the status should read "Not Started", If the % Complete column is 100% then the status should read "Complete", any percentage in the % Complete column that is not 0% or 100% should read "In Progress".  I'm using the formula below, I'm getting a value but it's not the correct value.  Any time I change the % Complete, it doesn't update.  Any suggestions would be appreciated.

 

Formula: =IF([% Complete]47 = "0%", "Not Started", IF([% Complete]47 = "100%", "Completed", "In Progress"))

 

Andrée Starå

Hi,

Welcome to Smartsheet and the Community!

Percentage is written as described below.

Use Percentage Format  to treat numbers as percentage of 1. If you format a cell this way, the value 0.95, for example, will be is displayed as 95%. The value 95 will be displayed as 9,500%.

Try this.

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

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Andree,

 

(Untested question) Having the quotes around the numbers in the formula is looking for specific text, but you are looking at a cell that contains a number. I know that when displaying a number to be used as such, you want to remove the quotes, but what about when looking for a number? Should the quotes be removed, or does it not affect things when going this way with it?

Miki Jeffers

Awesome.  It works perfectly now.  Thank you for the prompt reply

 

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée

Andrée Starå

Paul,

I missed removing the quotes, but it doesn't matter for this formula. It will work with or without.

Best,

Andrée

Paul Newcome

Good to know. Thanks!

Andrée Starå

Happy to help!

/A