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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!