Excel - Formula to find consecutive column that fulfills condition & Return Column Header

Question

I'm having trouble for formula to return column value. Where the number 1 has appeared 2x in a row, in this case need for fill formula for Column C.

While for column B I have used the Max Frequency formula

need formula in Column C

Thanks in advance

Answer 1

You could use Excel's texts functions; first CONCAT all cells with '0' and '1' (from you range), then FIND first occurrence of '11' (and arithmetic add +1 to the result) and you have desired number of week. Now last more CONCAT with text 'Week_' and optional wrap it all with IFERROR to not show error output if occurrence of '11' is not found.

Excel formula (without IFERROR):

CONCAT("Week_",FIND(11,CONCAT(D2:M2))+1)

Be aware that all cells in searched range D2:M2 must be non empty (either it has be '0' or '1')