Nested If Statement Not Working

I am trying to write a nested IF statement to automate the RYG status balls in a status column.

It will not work, it always say they are red. What am I doing wrong?


Logic Needed:

If less than 70% complete, "Red" status ball

If 70% or above and less than 100% complete, "Yellow" status ball

If 100% complete, "Green" status ball


Formulas I have tried:

=IF([% Complete]@row < 70, "Red", IF([% Complete]@row = 100, "Green", "Yellow"),"Yellow")

=IF([% Complete]@row < 70, "Red", IF(AND([% Complete]@row > 70, [% Complete]@row < 100), "Yellow", "Green"))


Results:


Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    edited 05/20/22

    @John Johnson

    In Smartsheet, % values are usually calculated as parts of 1. So 70% is really .7, 80% is really .8, and 100% is really 1. Your criteria starts with, "if the value is less than 70," which equates to 7000%, so everything is calculating to Red because they're all less than 7000%!

    Try this:

    =IF([% Complete]@row < .7, "Red", IF([% Complete]@row = 1, "Green", "Yellow"))

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • @Jeff Reisman ,


    Thank you! I knew it was something super simple and my brain was just not with it today.