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.
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
 ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response tag me @SoS  Dan Palenchar so I get notified of your reply!

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.
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
 ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response tag me @SoS  Dan Palenchar so I get notified of your reply!

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!