Nested If Statement Not Working

John Johnson

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:

Jeff Reisman

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