Tally neg or pos in a row

cloobless

Board Regular
Joined
Jul 15, 2014
Messages
66
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.

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

Some videos you may like

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
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Is it possible to have a 0 value? If so how should it be handled?

If no zeros maybe

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;background-color: #FAFAFA;;">value</td><td style="color: #333333;background-color: #FAFAFA;;">in-a-row</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">-10.53%</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">-2.32%</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">-2.32%</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">4.23%</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">-5.32%</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">-23.20%</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">8.30%</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2.33%</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1.33%</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">6.62%</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=IF(<font color="Blue">A3<0,IF(<font color="Red">AND(<font color="Green">A3<0,A2<0</font>),B2+1,1</font>),IF(<font color="Red">AND(<font color="Green">A3>0,A2>0</font>),B2+1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=IF(<font color="Blue">A4<0,IF(<font color="Red">AND(<font color="Green">A4<0,A3<0</font>),B3+1,1</font>),IF(<font color="Red">AND(<font color="Green">A4>0,A3>0</font>),B3+1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=IF(<font color="Blue">A5<0,IF(<font color="Red">AND(<font color="Green">A5<0,A4<0</font>),B4+1,1</font>),IF(<font color="Red">AND(<font color="Green">A5>0,A4>0</font>),B4+1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">=IF(<font color="Blue">A6<0,IF(<font color="Red">AND(<font color="Green">A6<0,A5<0</font>),B5+1,1</font>),IF(<font color="Red">AND(<font color="Green">A6>0,A5>0</font>),B5+1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B7</th><td style="text-align:left">=IF(<font color="Blue">A7<0,IF(<font color="Red">AND(<font color="Green">A7<0,A6<0</font>),B6+1,1</font>),IF(<font color="Red">AND(<font color="Green">A7>0,A6>0</font>),B6+1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">=IF(<font color="Blue">A8<0,IF(<font color="Red">AND(<font color="Green">A8<0,A7<0</font>),B7+1,1</font>),IF(<font color="Red">AND(<font color="Green">A8>0,A7>0</font>),B7+1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=IF(<font color="Blue">A9<0,IF(<font color="Red">AND(<font color="Green">A9<0,A8<0</font>),B8+1,1</font>),IF(<font color="Red">AND(<font color="Green">A9>0,A8>0</font>),B8+1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">=IF(<font color="Blue">A10<0,IF(<font color="Red">AND(<font color="Green">A10<0,A9<0</font>),B9+1,1</font>),IF(<font color="Red">AND(<font color="Green">A10>0,A9>0</font>),B9+1,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B11</th><td style="text-align:left">=IF(<font color="Blue">A11<0,IF(<font color="Red">AND(<font color="Green">A11<0,A10<0</font>),B10+1,1</font>),IF(<font color="Red">AND(<font color="Green">A11>0,A10>0</font>),B10+1,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jul 15, 2014
Messages
66
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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</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))

<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

Board Regular
Joined
Jul 15, 2014
Messages
66

ADVERTISEMENT

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
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Jul 15, 2014
Messages
66
Thank you both very much for taking the time to share you expertise with me. I deeply appreciate it.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top