formula decides whether there is 4 contagious non blank cell

[sony]

i have a 52 cells, say, A1~A52
i want to have the formula to let me know "yes" or "no"in cell A53 , whether there are 4 cell <> blank contagiously in any four cell out of the 52 cells.

what is the formula?

thanks

 

[kmillen]

Try this.

Paste this code into a VBA module.

Function Find4InARow() As Boolean
    Dim c As Range
    Dim count As Integer
   
    For Each c In ActiveSheet.Range("A1:A52")
        If Len(c.Value) > 0 Then
            count = count + 1
            If count > 3 Then
                Find4InARow = True
                Exit Function
            End If
        Else
            count = 0
        End If
    Next
    Find4InARow = False
End Function

In cell A53, enter (without the quotation marks) "=Find4InARow()" and press ENTER.

 

[Aladin Akyurek]

Re: formula decides whether there is 4 contagious non blank

i have a 52 cells, say, A1~A52
i want to have the formula to let me know "yes" or "no"in cell A53 , whether there are 4 cell <> blank contagiously in any four cell out of the 52 cells.

what is the formula?

thanks

A53:

Control+shift+enter...

=ISNUMBER(MATCH(TRUE,FREQUENCY(IF(A1:A52<>"",ROW(A1:A52)),IF(A1:A52="",ROW(A1:A52)))>=4,0))