If/And Function with a contained value
Hi Community,
I'm trying to create a formula that if the supply and return temps for Boiler 1 are out of spec, then it produces a red symbol. "Equipment Out of Service" contains 1, but temperatures are in spec, then it produces yellow symbol. If "Equipment Out of Service" does NOT contain 1, and temperatures are in spec then it produces a green symbol. I can't seem to nail it down without errors and I'm not the best stringing multiple parameters.
I would appreciate any help!
Best Answer

Try removing the quotes around your numbers... quotes will turn numbers into a text value, so the formula won't be able to evaluate if the cell is "greater than" a text value.
Ex. instead of <= "14", you'll want <= 14
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

It looks like you're just missing where to close off certain functions. When you use an AND function, you'll need to close off the AND before telling it what colour to do.
=IF(AND(this, that), "Red"
For example:
=IF(AND(CONTAINS(1, [Equipment Out of Service]@row), [Boiler 1 Supply Temperature]@row <= 175, [Boiler 1 Return Temperature]@row <= 145), "Red"
Then for your yellow, you just need to check if the first cell contains 1 or not (since you've already checked the temperature earlier). If you just look for the 1, then you know the temperature is not what you had specified earlier.
=IF(AND(CONTAINS(1, [Equipment Out of Service]@row), [Boiler 1 Supply Temperature]@row <= 175, [Boiler 1 Return Temperature]@row <= 145), "Red", IF(CONTAINS(1, [Equipment Out of Service]@row), "Yellow", "Green"))
Let me know if this produces the expected results!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thanks Genevieve, that makes sense and its worked! However, I've now encountered another issue where the formula I'm using isn't functioning correctly. For example,
According to the formula, Boiler 1 health should convey as green but for some reason its showing red?
Did I not properly breakup the formulas?

Try removing the quotes around your numbers... quotes will turn numbers into a text value, so the formula won't be able to evaluate if the cell is "greater than" a text value.
Ex. instead of <= "14", you'll want <= 14
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
That did the trick, thank you so much! You're a blessing

No problem! I'm glad I could help 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!