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

• ✭✭✭✭✭✭
edited 05/20/22

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!