Counting the number of series of consecutive positive and negatives numbers

[outofdebt]

Hey everyone.
I need help creating a macro or worksheet formula that will count the number of series of consecutive profits and losses in a dynamic column.

Sometimes there may be as few as 50 rows of profits and losses (in a random order) in the column; other times 500 rows or more. The column length is dynamic.

Suppose column A has the following profits and losses in cells A1 to A10, and for simplicity here each profit given a "(+)" and a loss a "(-)"

-174.56 (-)
796.36 (+)
- 63.77 (-)
- 14.96 (-)
95.63 (+)
69.23 (+)
47.85 (+)
-189.46 (-)
-479.63 (-)
-821.45 (-)

Each time the sign changes, a new consecutive series begins. So in this case, there are a total of 5 series of either consecutive profits or losses:
1st series = -174.56
2nd series = 796.36
3rd series = -63.77, -14.96
4th series = 95.63, 69.23, 47.85
5th series = -189.46, -479.63, -821.45

Any help is appreciated. I am considering using a "1" and "0" instead of + and -

Thanks.

 

[Weaver]

Welcome to the board!

How do you see the output from this exercise displayed?

 

[machopicho]

Set your sheet as:
<table width="452" border="0" cellpadding="0" cellspacing="0" height="241"><colgroup><col style="mso-width-source:userset;mso-width-alt:694;width:14pt" width="19"> <col style="mso-width-source:userset;mso-width-alt:1572;width:32pt" width="43"> <col style="mso-width-source:userset;mso-width-alt:877;width:18pt" width="24"> <col style="mso-width-source:userset;mso-width-alt:5522;width:113pt" width="151"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:14pt" width="19" align="center" height="20">
</td> <td class="xl68" style="border-left:none;width:32pt" width="43" align="center">A</td> <td class="xl68" style="border-left:none;width:18pt" width="24" align="center">B</td> <td class="xl68" style="border-left:none;width:113pt" width="151" align="center">C</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">1</td> <td class="xl70" style="border-top:none;border-left:none" align="center">-174,56</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(-)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">The 1° series has 1 element/s.</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">2</td> <td class="xl70" style="border-top:none;border-left:none" align="center">796,36</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(+)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">The 2° series has 1 element/s.</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">3</td> <td class="xl70" style="border-top:none;border-left:none" align="center">-63,77</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(-)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">4</td> <td class="xl70" style="border-top:none;border-left:none" align="center">-14,96</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(-)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">The 3° series has 2 element/s.</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">5</td> <td class="xl70" style="border-top:none;border-left:none" align="center">95,63</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(+)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">6</td> <td class="xl70" style="border-top:none;border-left:none" align="center">69,23</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(+)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">7</td> <td class="xl70" style="border-top:none;border-left:none" align="center">47,85</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(+)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">The 4° series has 3 element/s.</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">8</td> <td class="xl70" style="border-top:none;border-left:none" align="center">-189,46</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(-)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">9</td> <td class="xl70" style="border-top:none;border-left:none" align="center">-479,63</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(-)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;border-top:none" align="center" height="20">10</td> <td class="xl70" style="border-top:none;border-left:none" align="center">-821,45</td> <td class="xl68" style="border-top:none;border-left:none" align="center">(-)</td> <td class="xl71" style="border-top:none;border-left:none" align="center">The 5° series has 3 element/s.</td> </tr> </tbody></table>
Column C will be your output. Try this:

Sub test()
n = Cells(Rows.Count, 1).End(xlUp).Row
Range("C1:C" & n).ClearContents
For r = 1 To n
If Cells(r, 1).Value > 0 Then
i = i + 1
   If Cells(r + 1, 1).Value < 0 Or Cells(r + 1, 1).Value = "" Then
   k = k + 1
   Cells(r, 3) = "The " & k & "° series has " & i & " element/s."
   i = 0
   End If
End If
If Cells(r, 1).Value < 0 Then
j = j + 1
   If Cells(r + 1, 1).Value > 0 Or Cells(r + 1, 1).Value = "" Then
   k = k + 1
   Cells(r, 3) = "The " & k & "° series has " & j & " element/s."
   j = 0
   End If
End If
Next
     
End Sub

 

[outofdebt]

Welcome to the board!

How do you see the output from this exercise displayed?

Thanks for the reply Weaver. I guess I should mention that I've been a "long time lurker" here, and I have been reading many of the threads and stuff here for several months as I am trying to learn VBA for a big project I am working on.

THIS SITE IS AWESOME!!! (I don't know why I never registered until now... ?)

Also, I guess I should mention that I do have some coding experience in other languages, so I'm not a total noob as far as that goes.

Anyways ... back to my OP:
This is an input for a "Z-Score" formula that is part of a risk analysis spreadsheet project, BTW.

What I need the total number of consecutive series of either positive (profit) or negative (loss) cell values in a range of cells (which can be any column, BTW, I am just using A1:A10 here as illustration).

The ouptput will simply an integer value displayed in a single cell representing this total which will serve as an input to a larger formula, as I mentioned above.

I had a few ideas about using the worksheet function: COUNTIF(A1:A10, ">0") and "<0" to get the number of positive and negative values in the Range A1:A10. I do need that, but that isn't what my question is about.

I give another example, and hopefully this will clarify things a bit. Lets we have a series (which will be any random series, btw, not necessarily this one) of + and - values:
- + - ++ - - +++ (from left to right corresponding to cells A1:A10)

This first series is negative and consists of one cell (A1), the second series is positive and also consists of only one cell (A2), the third series has two positive cells, the fourth has two negative cells, and so on... here there are 6 series on consecutive numbers (either pos or neg) in the whole range.

What I am interested in here is total number of series (first, second, third ...) ... until we count the last series. So, hypothetically, if the last series is the eighty-seventh series, then the integer "87" is the number I am after. This is the desired output.

Again, thanks.

 

[outofdebt]

Set your sheet as:
<TABLE border=0 cellSpacing=0 cellPadding=0 width=452 height=241><COLGROUP><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" width=19><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 14pt; HEIGHT: 15pt" class=xl67 height=20 width=19 align=middle>


</TD><TD style="BORDER-LEFT: medium none; WIDTH: 32pt" class=xl68 width=43 align=middle>A</TD><TD style="BORDER-LEFT: medium none; WIDTH: 18pt" class=xl68 width=24 align=middle>B</TD><TD style="BORDER-LEFT: medium none; WIDTH: 113pt" class=xl68 width=151 align=middle>C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>1</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>-174,56</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(-)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>The 1° series has 1 element/s.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>2</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>796,36</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(+)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>The 2° series has 1 element/s.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>3</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>-63,77</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(-)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>4</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>-14,96</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(-)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>The 3° series has 2 element/s.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>5</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>95,63</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(+)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>6</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>69,23</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(+)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>7</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>47,85</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(+)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>The 4° series has 3 element/s.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>8</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>-189,46</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(-)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>9</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>-479,63</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(-)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl69 height=20 align=middle>10</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl70 align=middle>-821,45</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl68 align=middle>(-)</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl71 align=middle>The 5° series has 3 element/s.</TD></TR></TBODY></TABLE>
Column C will be your output. Try this:

Sub test()
n = Cells(Rows.Count, 1).End(xlUp).Row
Range("C1:C" & n).ClearContents
For r = 1 To n
If Cells(r, 1).Value > 0 Then
i = i + 1
   If Cells(r + 1, 1).Value < 0 Or Cells(r + 1, 1).Value = "" Then
   k = k + 1
   Cells(r, 3) = "The " & k & "° series has " & i & " element/s."
   i = 0
   End If
End If
If Cells(r, 1).Value < 0 Then
j = j + 1
   If Cells(r + 1, 1).Value > 0 Or Cells(r + 1, 1).Value = "" Then
   k = k + 1
   Cells(r, 3) = "The " & k & "° series has " & j & " element/s."
   j = 0
   End If
End If
Next
 
End Sub

Thanks for the reply. I may be able to get this ^^ to work if I total column C to get the integer value I need. I'll have to give it a try and see if I can make it work.

 

[outofdebt]

Couldn't edit my post (#4 in this thread) so I would like to add this to it:
The number of consecutive values in each series is completey random. They range from 1 consecutive value (pos or neg) to any number of consecutive values (all pos or neg). For example, there could be a series with 29 positives in a row, or 17 negatives in a row. It is 100% random, and each of these counts as a sinlge series and will add "1" to the total count. (A series of 29 cells all + or a series of 17 negative cells .... and so on)

 

[outofdebt]

*** UPDATE ***

Ok, I have figured how to modify machopicho's code to work for my application. I had to change it around a bit. As of now, it counts the number of series of consecutive pos or neg numbers and places them in column B. I divide the number of elements (contained in the counter variables "p" and "n") by their reciprocals so there are only 1's which will be tallied up to get the grand total of series, the number I am after.

Here is the code:

Sub CountConsecSeries()
 
    Dim r As Long
    Dim p As Integer, n As Integer 'p = positive counter, n = negative counter
 
    Sheets("Sheet3").Activate
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B1:B" & lastRow).ClearContents
 
    For r = 2 To lastRow
        If Cells(r, 1).Value > 0 Then
            p = p + 1
            If Cells(r - 1, 1) < 0 Then
                Cells(r - 1, 2) = n * (1 / n) 'will always equal "1"
                n = 0
            End If
        End If
 
        If Cells(r, 1).Value < 0 Then
            n = n + 1
                If Cells(r - 1, 1) > 0 Then
                    Cells(r - 1, 2) = p * (1 / p)
                    p = 0
                End If
        End If
 
    Next r
 
    ' for last remaining series after the loops
    If n > 0 Then
        Cells(r - 1, 2) = n * (1 / n)
    Else
        Cells(r - 1, 2) = p * (1 / p)
    End If
End Sub