Hello, I have a column of 1000+ rows, each row containing a 1 or 0. I am
trying to find a formula that locates the cell range of the largest sequence
of ones and use that range to sum the same rows in another column. The
largest sequence of 1's may occur a number of times. For example, column C
may contain: ...011011110100111100... The largest sequence is four 1's but
it occurs twice. I'd like to sum the corresponding rows (for 1111) in column
G and present the largest value of the 2 occurances. I hope that makes
sense. Thanks in advance. David

This is very complicated. I can get you there in 4 steps. 3 simple formulas
and one not so simple formula.

Assume your data is in the range A2:A18

You need 2 helper columns so I'll use B and C.

Enter this formula in B2 and copy down to B18: (leave B1 EMPTY or, at least
don't enter any numbers in B1). This will count the consecutive 1's:

=IF(A2=1,SUM(B1)+1,0)

Note: I used SUM(B1) just in case you have a TEXT header in B1. If B1 is
EMPTY then you can do without SUM(B1) and just use B1+1.

Enter this formula in C2 and copy down to C18. This will "flag" the max
consecutive 1's:

=IF(AND(A2=1,B2=SUMPRODUCT(MAX((A$2:A$18=1)*B$2:B$18))),ROW(),"")

Enter this formula in D2 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER) and copy down until you get blanks. This
formula will get the sum(s) that meet the criteria: (values to sum in
G2:G18)

=IF(ROWS($1:1)<=COUNT(C$2:C$18),SUM(OFFSET(G$2,MATCH(SMALL(C$2:C$18,ROWS($1:1)),C$2:C$18,0)-1,,-MAX(IF(A$2:A$18=1,B$2:B$18)))),"")

And finally, to get your result:

=MAX(D)

Biff

"DavidS" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello, I have a column of 1000+ rows, each row containing a 1 or 0. I am
> trying to find a formula that locates the cell range of the largest
> sequence of ones and use that range to sum the same rows in another
> column. The largest sequence of 1's may occur a number of times. For
> example, column C may contain: ...011011110100111100... The largest
> sequence is four 1's but it occurs twice. I'd like to sum the
> corresponding rows (for 1111) in column G and present the largest value of
> the 2 occurances. I hope that makes sense. Thanks in advance. David
>

It should be possible to do this with a single formula. For clarity
define the name "Freq" to refer to:

=FREQUENCY(IF(C1:C1500=1,ROW(C1:C1500)),IF(C1:C1500=0,ROW(C1:C1500)
,0))

Then enter in a cell:

=MAX(IF(Freq=MAX(Freq),SUBTOTAL(9,OFFSET(G:G,ROW(G1:G1500) -Freq
-1,,Freq))))

(Adjust the ranges according to the data layout.)

DavidS wrote:

> Hello, I have a column of 1000+ rows, each row containing a 1 or 0. I am
> trying to find a formula that locates the cell range of the largest sequence
> of ones and use that range to sum the same rows in another column. The
> largest sequence of 1's may occur a number of times. For example, column C
> may contain: ...011011110100111100... The largest sequence is four 1's but
> it occurs twice. I'd like to sum the corresponding rows (for 1111) in column
> G and present the largest value of the 2 occurances. I hope that makes
> sense. Thanks in advance. David

Lori and Biff, thanks very much for the formulas. They work (I expect you
knew that) - I just don't know how they work. I'll study them. I tried to
find a method to derive the formula but didn't come close. Is there an
information source you could point me to that will show/educate me how to
create such complex formulas. Many thanks. David

"DavidS" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello, I have a column of 1000+ rows, each row containing a 1 or 0. I am
> trying to find a formula that locates the cell range of the largest
> sequence of ones and use that range to sum the same rows in another
> column. The largest sequence of 1's may occur a number of times. For
> example, column C may contain: ...011011110100111100... The largest
> sequence is four 1's but it occurs twice. I'd like to sum the
> corresponding rows (for 1111) in column G and present the largest value of
> the 2 occurances. I hope that makes sense. Thanks in advance. David
>

That's pretty slick. When I first read this post I thought that Frequency
may be in play.

There's a bug however, if the max consecutive 1 is the last value in the
range the formula returns #N/A.

See these screencaps:

http://img114.imageshack.us/img114/4522/sumbug1py9.jpg

http://img506.imageshack.us/img506/2652/sumbug2tt0.jpg

Biff

"Lori" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> It should be possible to do this with a single formula. For clarity
> define the name "Freq" to refer to:
>
> =FREQUENCY(IF(C1:C1500=1,ROW(C1:C1500)),IF(C1:C1500=0,ROW(C1:C1500)
> ,0))
>
> Then enter in a cell:
>
> =MAX(IF(Freq=MAX(Freq),SUBTOTAL(9,OFFSET(G:G,ROW(G1:G1500) -Freq
> -1,,Freq))))
>
> (Adjust the ranges according to the data layout.)
>
> DavidS wrote:
>
>> Hello, I have a column of 1000+ rows, each row containing a 1 or 0. I am
>> trying to find a formula that locates the cell range of the largest
>> sequence
>> of ones and use that range to sum the same rows in another column. The
>> largest sequence of 1's may occur a number of times. For example, column
>> C
>> may contain: ...011011110100111100... The largest sequence is four 1's
>> but
>> it occurs twice. I'd like to sum the corresponding rows (for 1111) in
>> column
>> G and present the largest value of the 2 occurances. I hope that makes
>> sense. Thanks in advance. David
>

Ok, I figured out why that formula doesn't work as I've described in my
other post.

In the Subtotal function, the Row() argument has to have the same number of
elements returned by the Frequency function. As you may know, the Frequency
function returns an array of elements that is 1 more than the bins (for all
values greater than the highest interval). So, in the posted formula the
Subtotal row argument should be: ROW(C1:C1501).

Biff

"T. Valko" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> That's pretty slick. When I first read this post I thought that Frequency
> may be in play.
>
> There's a bug however, if the max consecutive 1 is the last value in the
> range the formula returns #N/A.
>
> See these screencaps:
>
> http://img114.imageshack.us/img114/4522/sumbug1py9.jpg
>
> http://img506.imageshack.us/img506/2652/sumbug2tt0.jpg
>
> Biff
>
> "Lori" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> It should be possible to do this with a single formula. For clarity
>> define the name "Freq" to refer to:
>>
>> =FREQUENCY(IF(C1:C1500=1,ROW(C1:C1500)),IF(C1:C1500=0,ROW(C1:C1500)
>> ,0))
>>
>> Then enter in a cell:
>>
>> =MAX(IF(Freq=MAX(Freq),SUBTOTAL(9,OFFSET(G:G,ROW(G1:G1500) -Freq
>> -1,,Freq))))
>>
>> (Adjust the ranges according to the data layout.)
>>
>> DavidS wrote:
>>
>>> Hello, I have a column of 1000+ rows, each row containing a 1 or 0. I am
>>> trying to find a formula that locates the cell range of the largest
>>> sequence
>>> of ones and use that range to sum the same rows in another column. The
>>> largest sequence of 1's may occur a number of times. For example, column
>>> C
>>> may contain: ...011011110100111100... The largest sequence is four 1's
>>> but
>>> it occurs twice. I'd like to sum the corresponding rows (for 1111) in
>>> column
>>> G and present the largest value of the 2 occurances. I hope that makes
>>> sense. Thanks in advance. David
>>
>
>

Biff - Thanks for pointing out the correction - obviously it should
also be array-entered and ranges fixed in the defined name too.

T. Valko wrote:

> Ok, I figured out why that formula doesn't work as I've described in my
> other post.
>
> In the Subtotal function, the Row() argument has to have the same number of
> elements returned by the Frequency function. As you may know, the Frequency
> function returns an array of elements that is 1 more than the bins (for all
> values greater than the highest interval). So, in the posted formula the
> Subtotal row argument should be: ROW(C1:C1501).
>
> Biff
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > That's pretty slick. When I first read this post I thought that Frequency
> > may be in play.
> >
> > There's a bug however, if the max consecutive 1 is the last value in the
> > range the formula returns #N/A.
> >
> > See these screencaps:
> >
> > http://img114.imageshack.us/img114/4522/sumbug1py9.jpg
> >
> > http://img506.imageshack.us/img506/2652/sumbug2tt0.jpg
> >
> > Biff
> >
> > "Lori" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> It should be possible to do this with a single formula. For clarity
> >> define the name "Freq" to refer to:
> >>
> >> =FREQUENCY(IF(C1:C1500=1,ROW(C1:C1500)),IF(C1:C1500=0,ROW(C1:C1500)
> >> ,0))
> >>
> >> Then enter in a cell:
> >>
> >> =MAX(IF(Freq=MAX(Freq),SUBTOTAL(9,OFFSET(G:G,ROW(G1:G1500) -Freq
> >> -1,,Freq))))
> >>
> >> (Adjust the ranges according to the data layout.)
> >>
> >> DavidS wrote:
> >>
> >>> Hello, I have a column of 1000+ rows, each row containing a 1 or 0. I am
> >>> trying to find a formula that locates the cell range of the largest
> >>> sequence
> >>> of ones and use that range to sum the same rows in another column. The
> >>> largest sequence of 1's may occur a number of times. For example, column
> >>> C
> >>> may contain: ...011011110100111100... The largest sequence is four 1's
> >>> but
> >>> it occurs twice. I'd like to sum the corresponding rows (for 1111) in
> >>> column
> >>> G and present the largest value of the 2 occurances. I hope that makes
> >>> sense. Thanks in advance. David
> >>
> >
> >