How do I use IF formula to change a colour based on a percentage column?
I have a status column with red, yellow and green. I'm needing a formula to change these colours when the percentage complete column gets below a certain percentage.
i.e. 150% Red
51%99% Yellow
100% Green
Answers

Something like this should work. Enter this formula in your status symbol column:
=IF([%Complete]1=100, "Green", IF([%Complete]1 < 51, "Red", "Yellow"))

@jg124 Smartsheet reads percentages as decimals, so 100% = 1, 50% = 0.5, etc. Otherwise your formula looks good.
=IF([%Complete]@row = 1, "Green", IF([%Complete]@row < 0.51, "Red", "Yellow"))

@jg124 thank you for posting  clean formula.
Is it possible to modify to make it if X% and end date is <1 day, "RED"
For example, if it's within 1 day of a task being due but only 50% complete, make it RED.

You would want to use something along the lines of...
=IF([%Complete]@row = 1, "Green", IF(AND([Due Date]@row <= TODAY(1), [%Complete]@row < 0.51), "Red", "Yellow"))

First, thank you so much for the modification. This is getting closer to what I'd like to do. 😀
Any chance you could elaborate a little on the formula  specifically, what does the value (1) represent next to Today? 
Also, trying to make it so that if the task is due within 3 days and less than 51% complete, then Red.
And would like to be able to manipulate the formula to say if it's 80% complete and same as above (within 3 days due), make it Yellow

TODAY(1) is basically saying TODAY + 1 or tomorrow. So if the Due Date is less than tomorrow.
=IF([%Complete]@row = 1, "Green", IF(AND([Due Date]@row <= TODAY(1), [%Complete]@row < 0.51), "Red", "Yellow"))
The above says...
If the % Complete = 100%, Green.
If the Due Date is less than tomorrow AND the % Complete is less than 50%, Red.
Everything else gets Yellow.
We can set it for less than 80% and within 3 days to turn yellow, but you will need to fill in the bold portion with whatever you want for something that doesn't fit within the three specified criteria sets.
=IF([%Complete]@row = 1, "Green", IF(AND([Due Date]@row <= TODAY(1), [%Complete]@row < 0.51), "Red", IF(AND([Due Date]@row <= TODAY(), [%Complete]@row < 0.8), "Yellow", "if all are false")))

@Paul Newcome  i modified a bit but getting Unparseable error  also not certain if it's correct syntax to write Today  3?
=IF([%Complete]@row = 1, "Blue", IF(AND([Due Date]@row <= TODAY3, [%Complete]@row < 0.51), "Red", IF(AND([Due Date]@row <= TODAY3, [%Complete]@row < 0.8), "Yellow", "Green")))
If task is marked 100% turn it Blue;
If task is <= 51% complete and it's within 3 days of End Date, turn it RED
If task is <= 80% complete and it's within 3 days of End Date, turn it YELLOW (will this be greater 51<>80%?)
If task is > 80% complete and <=Today, turn it Green

To subtract from TODAY, you would use
TODAY(3)
I am going to suggest using positive numbers inside of the TODAY function though. I have found that it helps (me) to think of dates as numbers where TODAY() = 0, Yesterday = (1), and Tomorrow = 1.
[Due Date]@row <= TODAY(3)
is basically saying the Due Date is three or more days in the past and not within the next 3 days. Does that make sense?

@Paul Newcome Yes! Ahh Thank you for explaining  makes sense.
I modified it as below  but still getting "unparseable" error  any idea what is missing?
=IF([%Complete]@row = 1, "Blue", IF(AND([Due Date]@row <= TODAY(3), [%Complete]@row < 0.51), "Red", IF(AND([Due Date]@row <= TODAY(3), [%Complete]@row < 0.8), "Yellow", "Green")))

I would start by double checking the column names. Is it [% Complete] or [%Complete]?
If that is not it, can you copy/paste the formula directly from the sheet exactly as it is?

Had double checked the column to ensure it was %Complete (no space)  and pasted exactly what I pasted in the cell  not sure if i have a syntax error?
Help :)

Depending on your region, you may need to swap commas out for semicolons and periods out for commas.
=IF([%Complete]@row = 1; "Blue"; IF(AND([Due Date]@row <= TODAY(3); [%Complete]@row < 0,51); "Red"; IF(AND([Due Date]@row <= TODAY(3); [%Complete]@row < 0,8); "Yellow"; "Green")))
Does that work?

Wait!!! Due DATE  it's titled End Date  that may be it  one sec will try

That very well could be it. If you don't have a column on your sheet called [Due Date] then you will get that error.

=IF([%Complete]@row = 1, "Blue", IF(AND([Finish]@row <= TODAY(3), [%Complete]@row < 0, 51), "Red", IF(AND([Finish]@row <= TODAY(3), [%Complete]@row < 0, 8), "Yellow", "Green")))
Above is new formula  getting an "Invalid Datatype" error :(
Help Article Resources
Categories
Check out the Formula Handbook template!