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

Sam,
Instead of 60% use 0.6.
Your formula should look like this:
=IF(Complete@row < 0.3, "Red", IF(Complete@row < 0.6, "Yellow", IF(Complete@row = 1, "Green")))
Also, what should happen with values 60%99%? With your current formula, if the value is 60%99%, you'll just get a blank cell, is that what you want?
Connor Hartford

Hi Sam,
Percentage is writen as 1 = 100%, 0,5 is 50% and so on. Or 0.5 and so on depending on your region.
Here's an example. It's important that the IF is in the correct order.
Try something like this.
=IF(Complete@row = 1; "Green"; IF(Complete@row < 0,3; "Red"; IF(Complete@row < 0,6; "Yellow")))
The same version but with the below changes for your and others convenience.
=IF(Complete@row = 1, "Green", IF(Complete@row < 0.3, "Red", IF(Complete@row < 0.6, "Yellow")))
Depending on your country you’ll need to exchange the comma to a period and the semicolon to a comma.
Did it work?
I hope that helps!
Have a fantastic weekend & Happy Holidays!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!
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.
Answers

Sam,
Instead of 60% use 0.6.
Your formula should look like this:
=IF(Complete@row < 0.3, "Red", IF(Complete@row < 0.6, "Yellow", IF(Complete@row = 1, "Green")))
Also, what should happen with values 60%99%? With your current formula, if the value is 60%99%, you'll just get a blank cell, is that what you want?
Connor Hartford

Hi Sam,
Percentage is writen as 1 = 100%, 0,5 is 50% and so on. Or 0.5 and so on depending on your region.
Here's an example. It's important that the IF is in the correct order.
Try something like this.
=IF(Complete@row = 1; "Green"; IF(Complete@row < 0,3; "Red"; IF(Complete@row < 0,6; "Yellow")))
The same version but with the below changes for your and others convenience.
=IF(Complete@row = 1, "Green", IF(Complete@row < 0.3, "Red", IF(Complete@row < 0.6, "Yellow")))
Depending on your country you’ll need to exchange the comma to a period and the semicolon to a comma.
Did it work?
I hope that helps!
Have a fantastic weekend & Happy Holidays!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!
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.

Hi Conner, thanks for your response!
I want status to be red if the %complete is 030%
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!

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.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives