Counting MAX number of consecutive blank cells

voitoosh

New Member
Joined
May 19, 2013
Messages
32
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!
 
Row LabelsNovember 2, 2015November 3, 2015November 4, 2015November 5, 2015November 6, 2015November 8, 2015November 11, 2015November 12, 2015November 13, 2015November 14, 2015November 15, 2015November 16, 2015November 17, 2015November 18, 2015November 19, 2015November 20, 2015November 21, 2015November 23, 2015November 24, 2015November 25, 2015November 26, 2015November 27, 2015November 29, 2015December 1, 2015December 5, 2015December 6, 2015December 7, 2015December 8, 2015December 9, 2015December 10, 2015December 11, 2015December 16, 2015December 17, 2015December 19, 2015December 21, 2015December 22, 2015December 23, 2015December 24, 2015December 25, 2015
344
42.8
10
13
14
21
41
51
55
57
412163010515
412307.52.51.5116.93.521
4140899
417743.51414710.510.53.53.54.54.8
4193918.818.810.6
421211010
423435.1
4235342

<colgroup><col><col span="23"><col span="16"></colgroup><tbody>
</tbody>
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))))
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
@ 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?
 
Upvote 0
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.

SAYILAR12
207
193
135
145
149
1661
206
139
1081
181
143
170
177
1611
931
1041
1671
1391
203
166
186
163
97
126
12911
189

<colgroup><col span="3"><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
@ mlokmanoglu



Book1
ABCDEFGHI
1SAYILAR12
2207
3193max / enbyk614
4135min / enkk27
5145
6149
71661
8206
9139
101081
11181
12143
13170
14177
151611
16931
171041
181671
191391
20203
21166
22186
23163
2497
25126
2612911
27189
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)))))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

JanFebMarAprMayJunJulAugSepOctNovDecMax BlanksTotal 1Total 2
1916 1613 15124910
12 1716 201061263
111412 18 6550
141915 1115181151030
1312 101482524

<colgroup><col span="12"><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top