# Problems using IF formula to calculate status

Options
edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭
edited 04/10/19
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

Excellent!

Happy to help!

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

Paul,

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

Best,

Andrée﻿

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

Good to know. Thanks!

• ✭✭✭✭✭✭
Options

Happy to help!

/A

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!