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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives