Nested Formula returning errors

Sonja Liebenberg
Sonja Liebenberg ✭✭✭
edited 12/13/21 in Formulas and Functions

Hi, Please help. I am pulling my hair out with frustration.

I am trying to do a nested formula for data in two columns under different circumstances to return a Fail or Pass result. I have managed to do the individual formulas, but now cannot get them combined into 1 formula. It is to monitor the Fridge and Freezer Temperatures in our Kitchens.

Column 1 - will be either a Fridge or Freezer

=IF([Type 1]@row = "Fridge", "True", "False")

=IF([Type 1]@row = "Freezer", "True", "False")

Column 2 - will be the Temperature measured.

I need a result in Column 3 which will say: - If Column 1 is a Fridge, and the Temp in Column 2 is Lower than 0 and larger than 4.5 then the result should be "Fail" And then if Column 1 is a Freezer, and Colmun 2 is lower than -18 or higher than -12, the result should be "Fail"

=IF([Fridge/Freezer 1]@row < 0, "Fail", IF([Fridge/Freezer 1]@row > 4.5, "Fail", "Pass"))

=IF([Fridge/Freezer 1]@row < -18, "Fail", IF([Fridge/Freezer 1]@row > -12, "Fail", "Pass"))


Your assistance will be appreciated

Sonja


Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Sonja Liebenberg ,


    Try something like this (replace column names if necessary):

    =IF(AND([column 1]@row = "Fridge", OR([column 2]@row < 0, [column 2]@row>4.5)),"Fail",IF(AND([column 1]@row = "Freezer", OR([column 2]@row < -18, [column 2]@row >-12)),"Fail","Pass"))

    This translates to:

    If column 1 is Fridge and column 2 is either less than 0 or greater than 4.5, fail. Otherwise, if column 1 is Freezer and column 2 is either less than -18 or greater than -12, fail. Otherwise, pass.


    Hope this helps! Let me know if it works for you.


    Best,

    Heather

  • Sonja Liebenberg
    Sonja Liebenberg ✭✭✭
    Answer ✓

    @Heather Duff You are a Godsend!!! Thank you so much. It works perfectly. Thank you So much

    Best Regards

    Sonja

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!