Problems using IF formula to calculate status

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"))

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…