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.

value	in-a-row
-10.53%	1
-2.32%	2
-2.32%	3
4.23%	1
-5.32%	1
-23.2%	2
8.3%	1
2.33%	2
1.33%	3
6.62%	4

<tbody>
</tbody>

 

[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
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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell 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)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.