Max number of consectutive blanks in a range
 

I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"")

In Column H, I like to count the maximum number of consecutive blanks in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18
to be returned.

Thanks,

Steve

Max number of consectutive blanks in a range
 

In H1 enter1
In H2 enter:
=IF(G2="",H1+1,0) and copy down

In another cell the answer is:
=MAX(H:H)
--
Gary''s Student - gsnu201001


"Steve" wrote:

I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"")

In Column H, I like to count the maximum number of consecutive blanks in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18
to be returned.

Thanks,

Steve

Max number of consectutive blanks in a range
 

Perfect.

I thought it'd be simple, but actually simplier than I thought.

Thanks again,

Steve

"Gary''s Student" wrote:

In H1 enter1
In H2 enter:
=IF(G2="",H1+1,0) and copy down

In another cell the answer is:
=MAX(H:H)
--
Gary''s Student - gsnu201001


"Steve" wrote:

I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"")

In Column H, I like to count the maximum number of consecutive blanks in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18
to be returned.

Thanks,

Steve

Max number of consectutive blanks in a range
 

You are welcome! You can use an array formula instead of the helper column,
but it might get really slow.
--
Gary''s Student - gsnu201001


"Steve" wrote:

Perfect.

I thought it'd be simple, but actually simplier than I thought.

Thanks again,

Steve

"Gary''s Student" wrote:

In H1 enter1
In H2 enter:
=IF(G2="",H1+1,0) and copy down

In another cell the answer is:
=MAX(H:H)
--
Gary''s Student - gsnu201001


"Steve" wrote:

I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"")

In Column H, I like to count the maximum number of consecutive blanks in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18
to be returned.

Thanks,

Steve

Max number of consectutive blanks in a range
 

This is fine.

Don't want
No
Slow

Thanks again.

"Gary''s Student" wrote:

You are welcome! You can use an array formula instead of the helper column,
but it might get really slow.
--
Gary''s Student - gsnu201001


"Steve" wrote:

Perfect.

I thought it'd be simple, but actually simplier than I thought.

Thanks again,

Steve

"Gary''s Student" wrote:

In H1 enter1
In H2 enter:
=IF(G2="",H1+1,0) and copy down

In another cell the answer is:
=MAX(H:H)
--
Gary''s Student - gsnu201001


"Steve" wrote:

I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"")

In Column H, I like to count the maximum number of consecutive blanks in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like 18
to be returned.

Thanks,

Steve

Max number of consectutive blanks in a range
 

Try this array formula** :

=MAX(FREQUENCY(IF(H3:H700="",ROW(H3:H700)),IF(H3:H 700<"",ROW(H3:H700))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"")

In Column H, I like to count the maximum number of consecutive blanks in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like
18
to be returned.

Thanks,

Steve

Max number of consectutive blanks in a range
 

This works nicely also.

Thank you very much.

Steve

"T. Valko" wrote:

Try this array formula** :

=MAX(FREQUENCY(IF(H3:H700="",ROW(H3:H700)),IF(H3:H 700<"",ROW(H3:H700))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"")

In Column H, I like to count the maximum number of consecutive blanks in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd like
18
to be returned.

Thanks,

Steve


.

Max number of consectutive blanks in a range
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
This works nicely also.

Thank you very much.

Steve

"T. Valko" wrote:

Try this array formula** :

=MAX(FREQUENCY(IF(H3:H700="",ROW(H3:H700)),IF(H3:H 700<"",ROW(H3:H700))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this formula in column G
=IF(SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N$7 00)0,SUMIF(Montana!$A$3:$A$700,C3,Montana!$N$3:$N $700),"")

In Column H, I like to count the maximum number of consecutive blanks
in
that entire range.
Meaning, there may 5 consective blanks, then 3, then 18, then 7. I'd
like
18
to be returned.

Thanks,

Steve


.