% Complete range in If statement how best to describe

Michele Deo
Michele Deo ✭✭
edited 12/09/19 in Formulas and Functions

I'm trying to have a conditional format occur in a status column, when the % Complete column hits a certain condition, for example:

1.  If % Complete = 100%, place "Complete" in the Status Column, and conditional format will change to blue

2. If % Complete is less than 99% and greater than 1%, then place "In Progress" in the Status column, and conditional format will change to green

3.  If % Complete is anything else, leave status blank

This is my formula, and I'm thinking I'm not representing the OR correctly, am I right?  Keeps coming back as UNPARSEABLE

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row < 0.99 OR IF([%Complete]@row > .01, "In Progress", "")))

 

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Michele,

    Try this.

    =IF([% Complete]@row = 1; "Complete"; IF(AND([% Complete]@row <= 0,99; [% Complete]@row >= 0,01); "In Progress"))    

    The same version but with the below changes for your and others convenience.  

    =IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row <= 0.99, [% Complete]@row >= 0.01), "In Progress"))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    I hope this helps you!

    I wish you a Happy Thanksgiving!

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!