Return Pass/Fail based on 3 consecutive passes; ignore blanks-CodePudding

I need to make a pass/fail sheet that returns a value in A1 based on the consecutive values in Row1. For example, if B1,C1,D1 are pass, A1=pass. But if B1, C1 are pass and D1 is fail, A1=Fail. Which I know how to do using IFs. BUT the thing I'm struggling with is that I need it to continue searching until it finds 3 consecutive passes or otherwise returns a fail.

So: B1=fail, C1=Pass, D1=Pass, E1=blank, F1=Pass needs to return a Pass for A1. There are no limits to how many attempts will be made to get 3 consecutive passes, but there will certainly be gaps in the days the tests are done.

I would prefer to do this with formulas, but I can make it a macro if there's no other way.

CodePudding user response：

Try using this formula, this shall find the consecutive Pass values as expected,

• Formula used in cell A1

=IF(ISNUMBER(SEARCH("PassPassPass",TEXTJOIN("",,$A$2:$A$6))),"Pass","Fail")