 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006Adding cells in one column based on a sequence in another
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:D) Biff > 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 [quoted text clipped - 4 lines] > 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.) > 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 [quoted text clipped - 4 lines] > 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 > It should be possible to do this with a single formula. For clarity > define the name "Freq" to refer to: [quoted text clipped - 21 lines] >> 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 > That's pretty slick. When I first read this post I thought that Frequency > may be in play. [quoted text clipped - 35 lines] >>> 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. > Ok, I figured out why that formula doesn't work as I've described in my > other post. [quoted text clipped - 46 lines] > >>> 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 > 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 [quoted text clipped - 4 lines] > 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
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.