Building an If/Than for a Pass Fail Based on a Cell Value

Options

Hello Community,

I work in a shoe factory in quality control and am currently using smartsheet to enter via form our test results for bonding strength for sole adhesion tests we do in our lab. I am trying to create a statement formula in which If the cell value is Greater than or equal to 80, it is a pass, less than it's a fail. That is an easy formula, no issue getting that to work. After I put it into place, I realized that sometimes, i'm only getting one sample (only one right shoe, no left or vice versa), so i can't base it off of both columns, the logic would have to be based on if i have entries for that value or not. If that makes sense....

Here's some pictures to help put it into place in a test sheet that i have:

Primary column is where I have my basic formula of =IF(AND([Column2]@row >= 80, [Column3]@row >= 80), "PASS", "FAIL").

Column 2 is an average of an array of cells where operators enter in their result for the left sample.

Column 3 is an average of an array of cells where operators enter in their result for the right sample.

Row 2 in this test has a value of zero for column 2 as we only received one sample, but the value in column 3 passes.

Is there a way to set up a formula so that when column2 equals 0, it only takes into account column3's data?

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey Walter

    Did you try just copy pasting my formula in? It looks like parentheses are out of place in the second IF (your original formula).

    kelly

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey Walter

    Using my formula, I cannot replicate the error. I can replicate this error, however, when I incorrectly position the parentheses. When you converted to the column formula - were you in the cell with the corrected formula?

    Please again check your formula, perhaps again trying to copy paste my formula above into your sheet.

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Walter Mootz

    =IF(AND(COUNTIFS([Column 2]@row:[Column 3]@row, @cell > 0) = 1, SUM([Column 2]@row:[Column 3]@row) >= 80), "PASS", IF(AND([Column 2]@row >= 80, [Column 3]@row >= 80), "PASS", "FAIL"))

    This allows for either column to be a zero and the score comes from the completed column. If both columns are populated then both scores are taken into account.

    Is this what you need?

    Kelly

  • Walter Mootz
    Walter Mootz ✭✭✭✭
    Options

    @Kelly Moore

    I ensured that i had the formula correct but got an invalid argument.

    Here is a snap of my formula copied from what you posted:

    Here is what I get when I hit enter:


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey Walter

    Did you try just copy pasting my formula in? It looks like parentheses are out of place in the second IF (your original formula).

    kelly

  • Walter Mootz
    Walter Mootz ✭✭✭✭
    Options

    I just suck and didn't realize i had that last parentheses in the wrong spot. Thank you Kelly for the fix. I appreciate it. I was close on what i was thinking originally, but mine involved way too many if statements compared to yours.


    Thank you so much!

  • Walter Mootz
    Walter Mootz ✭✭✭✭
    edited 03/04/22
    Options

    @Kelly Moore One thing i'm noticing, it works on the one where there's a zero in column 2, but not on the others.... I do have it set for a column formula, not an individual cell.



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hmmm. I’ll have to check later after meetings. When I tested last night I tested all combination of column entries and all worked.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey Walter

    Using my formula, I cannot replicate the error. I can replicate this error, however, when I incorrectly position the parentheses. When you converted to the column formula - were you in the cell with the corrected formula?

    Please again check your formula, perhaps again trying to copy paste my formula above into your sheet.

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!