Search code examples
excelvbaconditional-formatting

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

<Edited to correct my error about "RowA", whoops!>

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.

Solution

  • 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")

    FORMULA_SOLUTION