Formula Help

I am currently trying to build a formula that will take into account if both averages are greater than 80 OR a box is checked to then display the word Pass. This is the formula I previously used to account if the averages over 80, but something came up i wasn't expecting that also results in a pass so i added the checkbox.

Here is the original formula:

=IF(AND(COUNTIFS([Average Test Result Left]@row:[Average Test Result Right]@row, @cell > 0) = 1, SUM([Average Test Result Left]@row:[Average Test Result Right]@row) >= 80), "PASS", IF(AND([Average Test Result Left]@row >= 80, [Average Test Result Right]@row >= 80), "Pass", "Fail"))

Here is my modified version to which i receive an unparseable error. I think it's due to having and if/and/or statement:

=IF(AND(COUNTIFS([Average Test Result Left]@row:[Average Test Result Right]@row, @cell > 0) = 1, SUM([Average Test Result Left]@row:[Average Test Result Right]@row) >= 80) OR([Material Failure]@row=1)), "PASS", IF(AND([Average Test Result Left]@row >= 80, [Average Test Result Right]@row >= 80), "Pass", "Fail"))

Let me know if more information is needed to help solve the issue.

Any help is greatly appreciated.

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Walter Mootz

You'll want to add the OR function immediately before your AND, like so:

IF(OR(AND(statements), statements), "Pass", IF(AND(statements), "Pass", "Fail"))

So:

=IF(OR(AND(COUNTIFS([Average Test Result Left]@row:[Average Test Result Right]@row, @cell > 0) = 1, SUM([Average Test Result Left]@row:[Average Test Result Right]@row) >= 80), [Material Failure]@row=1), "PASS", IF(AND([Average Test Result Left]@row >= 80, [Average Test Result Right]@row >= 80), "Pass", "Fail"))

Let me know if this works for you!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Walter Mootz

You'll want to add the OR function immediately before your AND, like so:

IF(OR(AND(statements), statements), "Pass", IF(AND(statements), "Pass", "Fail"))

So:

Let me know if this works for you!

Cheers,

Genevieve

Walter Mootz

Worked perfectly Genevieve, I figured I just had my syntax wrong, i appreciate this so much!

Genevieve P.

No problem! I'm glad I could 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.