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. 1-50% 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 <= TODAY-3, [%Complete]@row < 0.51), "Red", IF(AND([Due Date]@row <= TODAY-3, [%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!
