# formula decides whether there is 4 contagious non blank cell

#### sony

##### Board Regular
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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### kmillen

##### Board Regular
Try this.

Paste this code into a VBA module.
Code:
``````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.

##### MrExcel MVP
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))

Replies
2
Views
324
Replies
5
Views
199
Replies
14
Views
740
Replies
6
Views
405
Replies
2
Views
709

1,191,165
Messages
5,985,039
Members
439,935
Latest member
Monty238

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back