Tally neg or pos in a row

[cloobless]

Hi, there. I'm stumped. I want a simple tally of a "negative run" or a "positive run" of values in a column, like this. Starting with the top row (not header), the "in a row" column is a count of how many negative or positive values occur in a row:

Any ideas of how to do this? Thank you very much.

[TABLE="width: 200"]
<tbody>[TR]
[TD]value[/TD]
[TD]in-a-row[/TD]
[/TR]
[TR]
[TD]-10.53%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]-2.32%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]-2.32%[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4.23%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]-5.32%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]-23.2%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8.3%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2.33%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1.33%[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6.62%[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Is it possible to have a 0 value? If so how should it be handled?

If no zeros maybe

Book1
	A	B
1	value	in-a-row
2	-10.53%	1
3	-2.32%	2
4	-2.32%	3
5	4.23%	1
6	-5.32%	1
7	-23.20%	2
8	8.30%	1
9	2.33%	2
10	1.33%	3
11	6.62%	4
Sheet1

Cell Formulas
Range	Formula
B3	=IF(A3<0,IF(AND(A3<0,A2<0),B2+1,1),IF(AND(A3>0,A2>0),B2+1,1))
B4	=IF(A4<0,IF(AND(A4<0,A3<0),B3+1,1),IF(AND(A4>0,A3>0),B3+1,1))
B5	=IF(A5<0,IF(AND(A5<0,A4<0),B4+1,1),IF(AND(A5>0,A4>0),B4+1,1))
B6	=IF(A6<0,IF(AND(A6<0,A5<0),B5+1,1),IF(AND(A6>0,A5>0),B5+1,1))
B7	=IF(A7<0,IF(AND(A7<0,A6<0),B6+1,1),IF(AND(A7>0,A6>0),B6+1,1))
B8	=IF(A8<0,IF(AND(A8<0,A7<0),B7+1,1),IF(AND(A8>0,A7>0),B7+1,1))
B9	=IF(A9<0,IF(AND(A9<0,A8<0),B8+1,1),IF(AND(A9>0,A8>0),B8+1,1))
B10	=IF(A10<0,IF(AND(A10<0,A9<0),B9+1,1),IF(AND(A10>0,A9>0),B9+1,1))
B11	=IF(A11<0,IF(AND(A11<0,A10<0),B10+1,1),IF(AND(A11>0,A10>0),B10+1,1))

 

[Rick Rothstein]

If cells A1 and B1 contain header text as shown, then put this formula in cell B2 and copy it down...

=IFERROR(IF(SIGN(A1)=SIGN(A2),B1+1,1),1)

 

[cloobless]

Is it possible to have a 0 value? If so how should it be handled?

If no zeros maybe

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]value[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]in-a-row[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-10.53%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-2.32%[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-2.32%[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4.23%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-5.32%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-23.20%[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8.30%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2.33%[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1.33%[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6.62%[/TD]
[TD="align: right"]4[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=IF(A3<0,IF(AND(A3<0,A2<0),B2+1,1),IF(AND(A3>0,A2>0),B2+1,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=IF(A4<0,IF(AND(A4<0,A3<0),B3+1,1),IF(AND(A4>0,A3>0),B3+1,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=IF(A5<0,IF(AND(A5<0,A4<0),B4+1,1),IF(AND(A5>0,A4>0),B4+1,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=IF(A6<0,IF(AND(A6<0,A5<0),B5+1,1),IF(AND(A6>0,A5>0),B5+1,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=IF(A7<0,IF(AND(A7<0,A6<0),B6+1,1),IF(AND(A7>0,A6>0),B6+1,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=IF(A8<0,IF(AND(A8<0,A7<0),B7+1,1),IF(AND(A8>0,A7>0),B7+1,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"]=IF(A9<0,IF(AND(A9<0,A8<0),B8+1,1),IF(AND(A9>0,A8>0),B8+1,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]=IF(A10<0,IF(AND(A10<0,A9<0),B9+1,1),IF(AND(A10>0,A9>0),B9+1,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=IF(A11<0,IF(AND(A11<0,A10<0),B10+1,1),IF(AND(A11>0,A10>0),B10+1,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Wow, thank you so much. I think a zero should count as positive -- yes, a zero could exist.

 

[cloobless]

Thank you very much, Rick. Scott above raised the possibility of zeros, which I had not addressed. Would the formula you suggest count a zero as "positive"? If so, I think it would work well. Thanks again.

 

[Rick Rothstein]

Thank you very much, Rick. Scott above raised the possibility of zeros, which I had not addressed. Would the formula you suggest count a zero as "positive"? If so, I think it would work well. Thanks again.

No, actually my formula treated zero as a separate category, but the following corrects the problem...

=IFERROR(IF(SIGN(A1+(A1=0))=SIGN(A2+(A2=0)),B1+1,1),1)

 

[Scott T]

Wow, thank you so much. I think a zero should count as positive -- yes, a zero could exist.

Then try this

=IF(A3<0,IF(AND(A3<0,A2<0),B2+1,1),IF(AND(A3>=0,A2>=0),B2+1,1))

 

[cloobless]

Thank you both very much for taking the time to share you expertise with me. I deeply appreciate it.