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.

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

"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.