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
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!