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

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
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
Categories
Check out the Formula Handbook template!