Formula Help

Walter Mootz

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.

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

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

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 🙂