COUNTBLANK MAX question
 

I need a formula (not a macro) so as to find the maximum number of blank
rows, appearing between text entries found between rows 1 & 100.
This, in refference to column A.
Ex:

Column A
Row 1 DATA
Row 2
Row 3 DATA (1 Blank cell appeared)
Row 4
Row 5
Row 6 DATA (2 Blank cells appeared)


So, in this case, I'd need for a formula to tell me that the
maximum number
of blank cells appearing between Row 1 & Row 6= 2.
Remember that any non blank cells appearing in column A will
contain text,
not numbers.

COUNTBLANK MAX question
 

With
A1:A100 containing values or blanks

I'm guessing this can be more streamlined, but...
Try this ARRAY FORMULA:

B1:
=MAX(LARGE(ISBLANK(A1:A100)*NOT(ISBLANK(A2:A101))* ROW(A1:A100),ROW(A1:A99))-LARGE(ISBLANK(A2:A101)*NOT(ISBLANK(A1:A100))*ROW(A 2:A101),ROW(A2:A100)))+1

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Vasilis Tergen" wrote:

I need a formula (not a macro) so as to find the maximum number of blank
rows, appearing between text entries found between rows 1 & 100.
This, in refference to column A.
Ex:

Column A
Row 1 DATA
Row 2
Row 3 DATA (1 Blank cell appeared)
Row 4
Row 5
Row 6 DATA (2 Blank cells appeared)


So, in this case, I'd need for a formula to tell me that the
maximum number
of blank cells appearing between Row 1 & Row 6= 2.
Remember that any non blank cells appearing in column A will
contain text,
not numbers.

COUNTBLANK MAX question
 

"Vasilis Tergen" skrev i en
meddelelse ...
I need a formula (not a macro) so as to find the maximum number of blank
rows, appearing between text entries found between rows 1 & 100.
This, in refference to column A.
Ex:

Column A
Row 1 DATA
Row 2
Row 3 DATA (1 Blank cell appeared)
Row 4
Row 5
Row 6 DATA (2 Blank cells appeared)


So, in this case, I'd need for a formula to tell me that the
maximum number
of blank cells appearing between Row 1 & Row 6= 2.
Remember that any non blank cells appearing in column A will
contain text,
not numbers.



Vasilis

Here's another option. Also an array formula:

=MAX(LARGE(IF(A1:A100<"",ROW(A1:A100),0),ROW(INDI RECT("1:"&ROWS(A1:A100)-1)))-
LARGE(IF(A1:A100<"",ROW(A1:A100),0),ROW(INDIRECT( "2:"&ROWS(A1:A100)))))-1

To be entered with <Shift<Ctrl<Enter, also if edited later.

If the value to look for is in e.g. B1 (in your example B1 would be empty),
the generic formula is:

=MAX(LARGE(IF(A1:A100<B1,ROW(A1:A100),0),ROW(INDI RECT("1:"&ROWS(A1:A100)-1)))-
LARGE(IF(A1:A100<B1,ROW(A1:A100),0),ROW(INDIRECT( "2:"&ROWS(A1:A100)))))-1

--
Best regards
Leo Heuser

Followup to newsgroup only please.