% Complete range in If statement how best to describe

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




  • 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!


    Andrée Starå

    Workflow Consultant @ Get Done Consulting


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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.