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
-
=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
-
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"))))
-
=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.
-
Perfect, thank you so much for the help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!