# 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?

• ✭✭✭✭✭✭
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

• ✭✭✭✭✭✭
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

• ✭✭✭✭✭✭
Options

=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

• ✭✭✭✭
Options

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:

• ✭✭✭✭✭✭
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

• ✭✭✭✭
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!

• ✭✭✭✭
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.

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭
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!