IF/AND/OR

I am in need of a formula to look at the Finish Type column and then calculate if it is in spec based on the finish type and if the gloss is in range for that finish type. (IE. PIF gloss range 22-86, COO gloss range 36-82, TRD gloss range 44-87). If the Finish type is not in the acceptable range for that type, "Out of Spec" or "In Spec".


I have 4 available selections in Finish Type, each with their own acceptable range for gloss.

Any help would be appreciated!


Best Answer

  • Intern98
    Intern98 ✭✭✭
    edited 04/06/22 Answer ✓

    =IF(AND([FINISH TYPE]@row = "PIF", [GLOSS]@row > 23, [GLOSS]@row < 41), "In Spec",

    IF(AND([FINISH TYPE]@row = "COO", [GLOSS]@row > 8, [GLOSS]@row < 20), "In Spec",

    IF(AND([FINISH TYPE]@row = "TRD", [GLOSS]@row > 20, [GLOSS]@row < 40), "In Spec",

    IF(AND([FINISH TYPE]@row = "LAST FINISH", [GLOSS]@row > 'lower limit, [GLOSS]@row < upper), "In Spec", "Out of Spec" )))

    Replacing last finish and the respective boundaries, lower upper limit, as needed

    do let me know if it worked.

Answers

  • Joanna Collins
    Joanna Collins ✭✭✭✭

    Here is the formula I tried....but failed

    =IF(AND([FINISH TYPE]@row = "PIF", GLOSS@row < 23, GLOSS@row > 41)), "out of spec", IF(AND([FINISH TYPE]@row = "COO", GLOSS@row < 8, GLOSS@row > 20)), "out of spec", IF(AND([FINISH TYPE]@row = "TRD", GLOSS@row < 20, GLOSS@row > 40)), "out of spec", "in spec"))))

  • Intern98
    Intern98 ✭✭✭
    edited 04/06/22 Answer ✓

    =IF(AND([FINISH TYPE]@row = "PIF", [GLOSS]@row > 23, [GLOSS]@row < 41), "In Spec",

    IF(AND([FINISH TYPE]@row = "COO", [GLOSS]@row > 8, [GLOSS]@row < 20), "In Spec",

    IF(AND([FINISH TYPE]@row = "TRD", [GLOSS]@row > 20, [GLOSS]@row < 40), "In Spec",

    IF(AND([FINISH TYPE]@row = "LAST FINISH", [GLOSS]@row > 'lower limit, [GLOSS]@row < upper), "In Spec", "Out of Spec" )))

    Replacing last finish and the respective boundaries, lower upper limit, as needed

    do let me know if it worked.

  • Joanna Collins
    Joanna Collins ✭✭✭✭

    Perfect, thank you so much for the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!