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
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 429 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!