# Tally neg or pos in a row

#### cloobless

##### Board Regular
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>

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Scott T

##### Well-known Member
Is it possible to have a 0 value? If so how should it be handled?

If no zeros maybe

#### Rick Rothstein

##### MrExcel MVP
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

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

If no zeros maybe

AB
1valuein-a-row
2-10.53%1
3-2.32%2
4-2.32%3
54.23%1
6-5.32%1
7-23.20%2
88.30%1
92.33%2
101.33%3
116.62%4

</tbody>
Sheet1

Worksheet Formulas
CellFormula
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))

</tbody>

<tbody>
</tbody>

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

#### cloobless

##### Board Regular

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

##### MrExcel MVP
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

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

Then try this
Code:
``=IF(A3<0,IF(AND(A3<0,A2<0),B2+1,1),IF(AND(A3>=0,A2>=0),B2+1,1))``

#### cloobless

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

Last edited:

Replies
6
Views
58
Replies
0
Views
41
Replies
6
Views
106
Replies
8
Views
80
Replies
3
Views
52