Need a formula to display a final test result for multiple text values

Lisa Matthews

Hello,

I need help with creating a formula to calculate a "final test result" for the following scenario:

1) If any cell in the row is “FAIL”, then result is “FAIL”

2) If all cells in the row are “PASS”, then result is “PASS”

3) If one cell is in the row is “N/A” or “blank”, then result is “INCOMPLETE”

4) If all cells in the row are “blank”, then the result is “NOT TESTED”

Thank you, in advance for your help!

--Lisa M.

ericncarr

Hi @Lisa Matthews,

I created 3 helper columns to accomplish this - Fail Count, Pass Count, and Blank Count

Fail Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, ="Fail")

Pass Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, ="Pass")

Blank Count: =COUNTIF([Pass/Fail Tester 1]@row:[Pass/Fail Tester 3]@row, OR(ISBLANK(@cell), @cell = "N/A"))

These will give us numerical values for pass/fail/blank/n/a within the range of Tester 1 to Tester 3 (or whatever range you want).

For the Final Test Result column, I have two formulas depending on how exactly you want it to work.

First, if you want it to say fail even if one of the values is blank or "N/A":

=IF([Fail Count]@row > 0, "Fail", IF([Pass Count]@row = 3, "Pass", IF(AND([Blank Count]@row > 0, [Blank Count]@row < 3), "Incomplete", "Not Tested")))

Second, if you want it to say incomplete until all values are filled in, even if there is a fail:

=IF(AND([Blank Count]@row > 0, [Blank Count]@row < 3), "Incomplete", IF([Pass Count]@row = 3, "Pass", IF([Fail Count]@row > 0, "Fail", "Not Tested")))