Formula: count maximum # consecutive blanks in column

[erik.van.geit]

Hi, formulagurus,

Can this be done in one formula without helper column?
If not I will build a User Defined Function.

   A      B  C      D  E          F      
 1 List 1    List 2    max blanks        
 2 data   0  data   0  List 1     List 2 
 3 data   0  data   0  3          4      
 4        1         1                    
 5        2  data   0                    
 6        3  data   0                    
 7 data   0         1                    
 8        1         2                    
 9 data   0         3                    
10        1  data   0                    
11 data   0  data   0                    
12 data   0  data   0                    
13 data   0         1                    
14 data   0         2                    
15 data   0  data   0                    
16 data   0  data   0                    
17        1  data   0                    
18        2  data   0                    
19        3  data   0                    
20 data   0         1                    
21        1         2                    
22        2         3                    
23 data   0         4                    
24 data   0  data   0                    

Sheet1
[Table-It] version 07 by Erik Van Geit

RANGE   FORMULA (1st cell)
B2:B24  =IF(A2="",B1+1,0)
D2:D24  =IF(C2="",D1+1,0)
E3      =MAX(B2:B24)
F3      =MAX(D2:D24)

[Table-It] version 07 by Erik Van Geit

Thank you for taking a look

kind regards,
Erik

 

[Richard Schollar]

Hi Eric

Try:

=MAX(FREQUENCY(IF(A2:A24="",ROW(A2:A24)),IF(A2:A24<>"",ROW(A2:A24))))

Confirmed with Ctrl+Shift+Enter

 

[facethegod]

Try

=MAX(FREQUENCY(COUNTIF(OFFSET(D1:D23,,,ROW(D1:D23)-ROW(D1)+1,1),">0"),ROW(D1:D23)-ROW(D1)+1))-1

 

[erik.van.geit]

Thank you, Richard

Saw this already a few times some time ago and thought: "why on earth would you need to know that?" Today I discovered that it would have been better to store that information anyway!! Searched for quite a long time on the Board without result.

I hope the post subject will help for those who are searching for this formula.

As for how the formula works: I studied it and looked to the helpfiles: seems like I will understand once the head is fresh again. If you would have a useful links which explains this approach, it would be welcome of course!

best regards,
Erik

 

[erik.van.geit]

Hi, facethegod, thanks for your input.
I don't know how to apply your formula, tried to move some ranges to no avail (perhaps too tired).

In the mean time I created a dynamic named range.

=MAX(FREQUENCY(IF(Names="",ROW(Names)),IF(Names<>"",ROW(Names))))