View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mal Mal is offline
external usenet poster
 
Posts: 17
Default 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