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

Walter Mootz

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?

Kelly Moore

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

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

Kelly Moore

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

@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

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

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

@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

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

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