Formula Help

Walter Mootz
Walter Mootz ✭✭✭✭
edited 06/17/22 in Formulas and Functions

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.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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
    Walter Mootz ✭✭✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! I'm glad I could help 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!