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
|