Adding cells in one column based on a sequence in another

[DavidS]

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

 

[T. Valko]

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

 

[Lori]

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]

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

 

[T. Valko]

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

 

[T. Valko]

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

 

[Lori]

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