Columns- count a consecutive series of positive or negative numbers and then get a sum of the max frequency

[mr_king]

[FONT=&quot]Columns- count a consecutive series of positive or negative numbers and then get a sum of the max frequency [/FONT]
[FONT=&quot]
Example:
[TABLE="width: 960"]
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]-3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[TD]-1[/TD]
[TD]-4[/TD]
[/TR]
</tbody>[/TABLE]
max positive count is 4 and the sum is 17.
max negative count is 5 and the sum is -10
Looking for excel array formula please for Microsoft office 2016.
Thanks!
[/FONT]

 

[DRSteele]

I have figured out how to get the streaks, but I can't seem to get the sum of the streaks. I'll keep working on it, so stay tuned.....

Book1
	A	B
3		longest streak
4	positives	4
5	negatives	5
Sheet69

Cell Formulas
Range	Formula
B4	{=MAX(FREQUENCY(IF(A1:M1>0,COLUMN(A1:M1)),IF(A1:M1<=0,COLUMN(A1:M1))))}
B5	{=MAX(FREQUENCY(IF(A1:M1<0,COLUMN(A1:M1)),IF(A1:M1>=0,COLUMN(A1:M1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

The rather painful formulas in C4:C5 will calculate the sum of the longest streaks, given the formulas that DRSteele provided. It might be possible to simplify them, but probably not by much. I'd recommend you update the macro in your other thread to perform this calculation too.

	A	B	C	D	E	F	G	H	I	J	K	L	M
longest streak	Sum of longest streak
positives
negatives

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]{=MAX(FREQUENCY(IF(A1:M1>0,COLUMN(A1:M1)),IF(A1:M1<=0,COLUMN(A1:M1))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]{=SUM(OFFSET(A1,0,MATCH(B4,MMULT(--(A1:M1>0),TRANSPOSE(IF((COLUMN(A1:M1)>=TRANSPOSE(COLUMN(A1:M1)))*(COLUMN(A1:M1)<=TRANSPOSE(COLUMN(A1:M1))+B4-1),1,0))),0)-1,1,B4))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]{=MAX(FREQUENCY(IF(A1:M1<0,COLUMN(A1:M1)),IF(A1:M1>=0,COLUMN(A1:M1))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]{=SUM(OFFSET(A1,0,MATCH(<fo
B5,MMULT(--(A1:M1<0),TRANSPOSE(IF((COLUMN(A1:M1)>=TRANSPOSE(COLUMN(A1:M1)))*(COLUMN(A1:M1)<=TRANSPOSE(COLUMN(A1:M1))+B5-1),1,0))),0</fo
)-1,1,B5))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

There is a conceptual problem: you might have several sets of longest streaks (of say 4), but they will sum to different values. Should then the largest of the sums of the longest streaks be determined?

 

[Eric W]

The formulas above in post 3 will only find the sum of the first streak of maximum length. Finding other equal length streaks and comparing values is probably beyond the scope of a single cell formula. I can see how it would be done with some helper cells, but that would still be pretty clumsy. I think a macro could do this cleaner.

 

[mr_king]

Hello DRSteel, yes they should. Thanks.

 

[mr_king]

Thanks for your assistance Eric W