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!

Find more posts tagged with

Accepted answers

Intern98

=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.

All comments

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

=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

Perfect, thank you so much for the help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.