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

«13

Answers

  • jg124
    jg124 ✭✭✭✭✭
    edited 05/07/20

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

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

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

  • @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
    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?

  • @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
    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?

  • 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
    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?

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

  • Paul Newcome
    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.

  • @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 :(

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!