How do I use IF formula to change a colour based on a percentage column?

Arama Eruera

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

jg124

Something like this should work. Enter this formula in your status symbol column:

=IF([%Complete]1=100, "Green", IF([%Complete]1 < 51, "Red", "Yellow"))

Paul Newcome

@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"))

Tania Cubell

@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.

Paul Newcome

@Tania Cubell

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"))

Tania Cubell

@Paul Newcome

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

Paul Newcome

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")))

Tania Cubell

@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

Paul Newcome

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?

Tania Cubell

@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")))

Paul Newcome

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?

Tania Cubell

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 :)

@Paul Newcome

Paul Newcome

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?

Tania Cubell

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

Paul Newcome

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.

Tania Cubell

@Paul Newcome

=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 :(