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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin
    Answer ✓

    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

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

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! I'm glad I could help 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!