What is wrong with my formula?
I'm trying to do multiple IF formulas to show a Green, Yellow, or Red traffic light based on a % complete column. I can get "Red" (<70%) to show by itself, "Green" (100%) to show by itself, but I can't get the yellow range or all three together. What is wrong below?
=IF([% Complete]3 = 1, "Green"), =IF([% Complete]3 <1 AND >=.7, "Yellow"), =IF([% Complete]3 < .7, "Red")
Thank you
Best Answers

Logical statements are evaluated in order and stop once any condition is true, so you don't need the AND statement for your yellow. You can use this
=IF([% Complete]@row = 1, "Green", IF([% Complete]@row >= 0.7, "Yellow", "Red"))
The way you wrote your AND statement is incorrect though, the AND should be before the logical statements and each separated by a comma, it would look like this
=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row < 1, [% Complete]@row >= 0.7), "Yellow", "Red"))
Though it is unnecessary in this case and it is a best practice to keep your formulas as simple as possible.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Thank you for the response. I did figure it out but your formula is less complicated and better structured. I tried to correct the order but failed with many different formulas.
All the best,
Dennis
Answers

Logical statements are evaluated in order and stop once any condition is true, so you don't need the AND statement for your yellow. You can use this
=IF([% Complete]@row = 1, "Green", IF([% Complete]@row >= 0.7, "Yellow", "Red"))
The way you wrote your AND statement is incorrect though, the AND should be before the logical statements and each separated by a comma, it would look like this
=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row < 1, [% Complete]@row >= 0.7), "Yellow", "Red"))
Though it is unnecessary in this case and it is a best practice to keep your formulas as simple as possible.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Thank you for the response. I did figure it out but your formula is less complicated and better structured. I tried to correct the order but failed with many different formulas.
All the best,
Dennis
Help Article Resources
Categories
Check out the Formula Handbook template!