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

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

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

=COUNTBLANK(A1:A16)

"Mal" wrote:

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

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

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:A 12))))+(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)

"Mal" wrote in message
...
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

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

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))))

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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:A 12))))+(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)

"Mal" wrote in message
...
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

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

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

"T. Valko" wrote in message
...
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))))

--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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:A 12))))+(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)

"Mal" wrote in message
...
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

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

Hello,

If only real blank cells qualify, array-enter:
=MAX(FREQUENCY(ISBLANK(rng)*ROW(rng),NOT(ISBLANK(r ng))*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,L OOKUP(2,1/
(FALSE=ISBLANK(rng)),ROW(rng)))

and apply the MAX formula to rng2.

Regards,
Bernd