Ignoring Blank fields on a Pass or Fail UAT sheet

David White

Hi All,

I have a sheet i am building for user acceptance testing of a program. We have various differing numbers of steps per test, and need to combine these steps with an overall pass or fail.

As we have some tests that have Statuses that are blank i need my formula to ignore the blanks and "Pass" them, it would only fail if tester assigns a fail to one of these statuses.

As step 8 status is blank, the overall test status is coming back as a fail. Which is incorrect.

My formula is:

=IF(AND([Step 10 - Status]@row = "Pass", [Step 9 - Status]@row = "Pass", [Step 8 - Status]@row = "Pass", [Step 7 - Status]@row = "Pass", [Step 6 Status]@row = "Pass", [Step 5 - Status]@row = "Pass", [Step 4 - Status]@row = "Pass", [Step 3 - Status]@row = "Pass", [Step 2 - Status]@row = "Pass", [Step 1 - Status]@row = "Pass"), "Pass", "Fail")

I am wondering if i need to add in ISBLANK or is there a better neater way?

Thank you

David White

Hi Andree,

Yes that works perfectly, thank you. Hadnt thought of using Countif in this way. A good lesson!

Thank you

David

Andrée Starå

Hi @David White

I hope you're well and safe!

Is there anything else in the range that could include "Fail"?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

David White

Hi Andree,

Each one of the status fields could be classified as Pass, Fail or Blank, dependent on the number of steps in each test. No other columns use these nominated identifiers, they are free text for explanations on the test.

I hope that helps?

Thank you

David

Andrée Starå

@David White

Happy to help!

Would something like this work? (add another for Pass)

=IF(COUNTIF([Step 1 - Status]@row:[Step 10 - Status]@row, "Fail") > 0, 1)

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

David White

Thanks Andree,

I am not sure how the countis would work, (my lack of understanding) and how we switch the 0.1 to pass fail?

I was thinking more along the lines of:

=IF((OR(ISBLANK([Step 10 - Status]@row), ISBLANK([Step 9 - Status]@row),ISBLANK([Step 8 - Status]@row)), AND([Step 10 - Status]@row = "Pass", [Step 9 - Status]@row = "Pass",[Step 8 - Status]@row = "Pass")), "Pass", "Fail")

But this seems to be on parameter off too.

Any ideas?

Andrée Starå

@David White

You're more than welcome!

I understand the confusion because I forgot to add the Fail or Pass result text.

Try this.

=IF(COUNTIF([Step 1 - Status]@row:[Step 11 - Status]@row, "Fail") > 0, "Fail", IF(COUNTIF([Step 1 - Status]@row:[Step 11 - Status]@row, "Pass") > 0, "Pass"))

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

David White

Hi Andree,

Yes that works perfectly, thank you. Hadnt thought of using Countif in this way. A good lesson!

Thank you

David

David White

We have also developed this which works too!

=IF((OR((OR(ISBLANK([Step 10 - Status]@row), ISBLANK([Step 9 - Status]@row), ISBLANK([Step 8 - Status]@row))))), "Pass", "Fail")

Lots more complicated :)

Andrée Starå

@David White

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!