Maximum Number of Blank Cells between Non Blank Cells in a Range

[Mal]

I am trying to count the Maximum Number of Blank Cells between Non Blank
Cells in a Range.
e.g.
Range A1:a13
a1 - 2
a3 - blank
a4 - blank
a5 - 24
a6 - blank
a7 - 7
a8 - 51
a9 - blank
a10 - blank
a11 blank
a12 - blank
a13 - 6
The answer is 4. Cells a9 to a12.
Any help appreciated.
Thanks,
Mal

 

[Teethless mama]

=COUNTBLANK(A1:A16)

 

[Ron Coderre]

This ARRAY FORMULA, committed with Ctrl+Shift+Enter (instead of just Enter)
returns the count of the maximum consecutive blanks (or empty strings):

=MAX(FREQUENCY(IF((A1:A12="")*(A1:A12=A2:A13),ROW(A1:A12)),IF((A1:A12<>"")*(A1:A12<>A2:A13),ROW(A1:A12))))+(COUNTBLANK(A1:A13)>0)

Since text wrap may impact the display, there are no spaces in that formula.
Also, here is that same formula, broken into sections:
=MAX(FREQUENCY(IF((A1:A12="")*(A1:A12=A2:A13),
ROW(A1:A12)),IF((A1:A12<>"")*(A1:A12<>A2:A13),
ROW(A1:A12))))+(COUNTBLANK(A1:A13)>0)

Using your posted data,
that formula returns: 4

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[T. Valko]

This simplified version also works (array entered):

Note that both empty cells and cells that may contain formula blanks are
considered "blanks".

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

 

[Mal]

Thanks for your help people.
Problem solved.
Regards,
Mal

 

[Bernd P]

Hello,

If only real blank cells qualify, array-enter:
=MAX(FREQUENCY(ISBLANK(rng)*ROW(rng),NOT(ISBLANK(rng))*ROW(rng)))

And if you *really* meant "between non blank cells" then define, for
example, a name rng2:
=INDEX(rng,MATCH(FALSE,ISBLANK(rng),)):INDEX(rng,LOOKUP(2,1/
(FALSE=ISBLANK(rng)),ROW(rng)))

and apply the MAX formula to rng2.

Regards,
Bernd