If/And Function with a contained value

Bryan Letourneau

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!

Genevieve P.

Hi @Bryan Letourneau

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

Genevieve P.

Hi @Bryan Letourneau

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

Bryan Letourneau

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 break-up the formulas?

Genevieve P.

Hi @Bryan Letourneau

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

Bryan Letourneau

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

Genevieve P.

No problem! I'm glad I could help 🙂