Counting MAX number of consecutive blank cells

[voitoosh]

Hi All,
I have an Excel 2010-related problem.
I need some tips on how to solve this. I have tried some formulas but it only puzzled me :/

I have a row of data, e.g.

WORK WORK WORK WORK __ __ __ __ __ OFF OFF OFF OFF __ __ __ LVE LVE __ WORK WORK WORK. -> this should return 5, as it is the biggest "block" of consecutive blank cells.

I need to calculate what is the MAX number of blank cells in this row (I have tried countifs, MAX etc) but there is probably one long formula to do so. Also, these (blanks) appear in a different places throughout the row (it is basically a roster of a person).

Any ideas how to tackle this without VBA (in which I am rubbish)?

Help much appreciated!

Thanks a lot!

 

[tkraft]

[TABLE="width: 5103"]
<colgroup><col><col span="23"><col span="16"></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD="align: right"]November 2, 2015[/TD]
[TD="align: right"]November 3, 2015[/TD]
[TD="align: right"]November 4, 2015[/TD]
[TD="align: right"]November 5, 2015[/TD]
[TD="align: right"]November 6, 2015[/TD]
[TD="align: right"]November 8, 2015[/TD]
[TD="align: right"]November 11, 2015[/TD]
[TD="align: right"]November 12, 2015[/TD]
[TD="align: right"]November 13, 2015[/TD]
[TD="align: right"]November 14, 2015[/TD]
[TD="align: right"]November 15, 2015[/TD]
[TD="align: right"]November 16, 2015[/TD]
[TD="align: right"]November 17, 2015[/TD]
[TD="align: right"]November 18, 2015[/TD]
[TD="align: right"]November 19, 2015[/TD]
[TD="align: right"]November 20, 2015[/TD]
[TD="align: right"]November 21, 2015[/TD]
[TD="align: right"]November 23, 2015[/TD]
[TD="align: right"]November 24, 2015[/TD]
[TD="align: right"]November 25, 2015[/TD]
[TD="align: right"]November 26, 2015[/TD]
[TD="align: right"]November 27, 2015[/TD]
[TD="align: right"]November 29, 2015[/TD]
[TD="align: right"]December 1, 2015[/TD]
[TD="align: right"]December 5, 2015[/TD]
[TD="align: right"]December 6, 2015[/TD]
[TD="align: right"]December 7, 2015[/TD]
[TD="align: right"]December 8, 2015[/TD]
[TD="align: right"]December 9, 2015[/TD]
[TD="align: right"]December 10, 2015[/TD]
[TD="align: right"]December 11, 2015[/TD]
[TD="align: right"]December 16, 2015[/TD]
[TD="align: right"]December 17, 2015[/TD]
[TD="align: right"]December 19, 2015[/TD]
[TD="align: right"]December 21, 2015[/TD]
[TD="align: right"]December 22, 2015[/TD]
[TD="align: right"]December 23, 2015[/TD]
[TD="align: right"]December 24, 2015[/TD]
[TD="align: right"]December 25, 2015[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41216[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41230[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.9[/TD]
[TD][/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]41408[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41774[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10.5[/TD]
[TD="align: right"]10.5[/TD]
[TD="align: right"]3.5[/TD]
[TD][/TD]
[TD="align: right"]3.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41939[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]18.8[/TD]
[TD="align: right"]10.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42121[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42343[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42353[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi Aladin,
Sorry for the late response. I have attached an example of a data set I am working on. I am attempting to compute the max number of consecutive blank cells after a 1st date and before a last number in a range of values. Would you have any ideas?

Thanks!
Tim

Control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(A2:K2="",COLUMN(A2:K2)),IF(A2:K2<>"",COLUMN(A2:K2))))

 

[Aladin Akyurek]

@ tkraft

--3---7
6----2--6
9----

The - bit stands for an empty cell. What are your expected results for each row of the above data?

 

[mlokmanoglu]

Aladin Bey Merhaba,

Excel formülü konusunda yardıma ihtiyacım var, aşağıdaki örnekte 1 nolu sütundaki boşlukların frekansı lazım. Mesela an fazla 6 defa üst üste boşluk gemiş iken en az 2 defa üst üste boşluk gemiş. Bunu hesaplayacak formülü bulamıyorum. Teşekkür ederim.

[TABLE="width: 285"]
<colgroup><col span="3"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SAYILAR[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]207[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]193[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]206[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]181[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]143[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]177[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]203[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]186[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]163[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]189[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

@ mlokmanoglu

Book1
	A	B	C	D	E	F	G	H	I
1				SAYILAR	1	2
2	207
3	193						max / enbyk	6	14
4	135						min / enkk	2	7
5	145
6	149
7	166				1
8	206
9	139
10	108				1
11	181
12	143
13	170
14	177
15	161				1
16	93					1
17	104					1
18	167					1
19	139				1
20	203
21	166
22	186
23	163
24	97
25	126
26	129				1	1
27	189
Sheet1

H3 >>> I3, control+shift+enter, boş hücrelerin en fazla peş peşe gelme frekansı (maximum number of consecutive blanks)…

=MAX(FREQUENCY(IF(E2:E26="",ROW(E2:E26)),IF(1-(E2:E26=""),ROW(E2:E26))))


H4 >>> I4, , control+shift+enter, boş hücrelerin en az peş peşe gelme frekansı (minimum number of consecutive blanks)…

=MIN(IF(FREQUENCY(IF(E2:E26="",ROW(E2:E26)),IF(1-(E2:E26=""),ROW(E2:E26))),FREQUENCY(IF(E2:E26="",ROW(E2:E26)),IF(1-(E2:E26=""),ROW(E2:E26)))))

 

[mlokmanoglu]

Aladin Bey, I cannot tell you how much you helped me. I've been wasting time on this formula for a long time. Thank you very much for your help. Best regards.

 

[Aladin Akyurek]

Aladin Bey, I cannot tell you how much you helped me. I've been wasting time on this formula for a long time. Thank you very much for your help. Best regards.

You are welcome.

 

[rohan281]

Control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(A2:K2="",COLUMN(A2:K2)),IF(A2:K2<>"",COLUMN(A2:K2))))

Hi Aladin,

is there any way to find out the position of cells for the maximum consecutive cells? I need to find out the maximum consecutive blank cells in a range and the position of first and last blank cell of that sequence.

For ex. if row has data such as 1 1 1 Blank Blank 1 1 Blank Blank Blank 1 1
so the max consecutive blank cells would be 3 and position for first blank cell in that range would be 8 and last cell would be 10.

 

[rohan281]

Basically what i want is sum of data based on max consecutive blank cells. If the range has 6 or more consecutive blanks then Total 1 should be sum of numbers appearing before this sequence of blank cells and Total 2 should be sum of numbers appearing after the sequence of these blank cells. if Max consecutive blanks are less than 6 then Total 1 should be sum of all the numbers in the range and total 2 should be 0.

[TABLE="width: 973"]
<colgroup><col span="12"><col><col span="2"></colgroup><tbody>[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Max Blanks[/TD]
[TD]Total 1[/TD]
[TD]Total 2[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]16[/TD]
[TD]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]91[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]17[/TD]
[TD]16[/TD]
[TD] [/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]14[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD]55[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]19[/TD]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11[/TD]
[TD]15[/TD]
[TD]18[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]103[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10[/TD]
[TD]14[/TD]
[TD]8[/TD]
[TD]25[/TD]
[TD]24[/TD]
[/TR]
</tbody>[/TABLE]

 

[Benobi7]

Hi,

I am trying to do similar to what a couple of people have posted in this thread. I am trying to calculate the max consecutive blank within a range, with both the start and end points of this range being dynamic.

Simply, if the following appeared, I would want to return the value of two, not 5, or 9. Please note the starting and ending positions of the range would have to change dynamically.

- - - - - 8 5 2 1- - 4 6 8 2 - - - - - - - - -

How can I do this?

Thank you,

Ben