issue applying conditional formatting to "% Complete" column

I am trying to write a formula that will change the symbol in the status column based on the percentage, as indicated by the "Complete" column.

I cannot get my sheet to respond to any of my parameters.

I have tried these formulas:

=IF(Complete@row < 30, "Red", IF(Complete@row < 60, "Yellow", IF(Complete@row = 100, "Green")))

=IF(Complete@row < 30%, "Red", IF(Complete@row < 60%, "Yellow", IF(Complete@row = 100%, "Green")))

=IF(Complete@row < "30%", "Red", IF(Complete@row < "60%", "Yellow", IF(Complete@row = "100%", "Green")))


I imagine this has something to do with the "%" sign because the formula that I used for the "Difference" column which was a dollar figure worked fine


Best Answers

Answers

  • Hi Conner, thanks for your response!


    I want status to be red if the %complete is 0-30%

    Status should be yellow if %Complete is 31 - 99%

    status should be red if %Complete is 100%

    here is my current formula:

    =IF(Complete@row < 0.3, "Red", IF(Complete@row <= 0.9, "Yellow", IF(Complete@row = 1, "Green")))

  • I figured it out! Here's what I settled on:


    =IF(Complete@row < 0.3, "Red", IF(Complete@row < 1, "Yellow", IF(Complete@row = 1, "Green")))

  • Thanks again for your help!

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

    I'm always happy to help!

    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.