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
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

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

That did the trick, thank you so much! You're a blessing

No problem! I'm glad I could help 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!