consecutive

[gotta know]

Is it possible for Excel to look at a column of results, and determine
the number of times I had consecutive winning or losing streaks from my
stock speculation?

My column of data either has a PLUS figure, a MINUS figure, or more
times than not, a blank "" result, representing no trading.

For example, the "x" represents the rusults I need:

Consec. W/L Win Frequency Loss Frequency
1 x x
2 x x
3 x x
4
5
etc.

Thanks again,
Erick

 

[JMB]

Assuming blanks are ignored, ie

+
+
Blank
+

counts as 3 consecutive gains, you could try the custom function below to
get the information you want (if you don't want to ignore blanks, it can
modified).

If you are new to VBA, then see David McRitchies site for some tutorials.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

The NG will wrap the function, so when you paste it into a VBA module watch
for lines that appear on two lines that s/b on one line (they'll probably be
in red).

Syntax is
=consec($A$9:$A$36,"+",$A2)
where A9:A36 is your column of results, "+" is what you are looking for, and
A2 is a reference to the number of consecutive occurences you want to find.



Function Consec(rngData As Range, varValue As Variant, lngCount As Long) As
Long
Dim strDelimiter As String
Dim arrData() As Variant
Dim varData As Variant
Dim i As Long

strDelimiter = Space(1)
ReDim arrData(1 To rngData.Cells.Count)

If varValue = strDelimiter Then Exit Function
If Not (rngData.Rows.Count = 1 Xor _
rngData.Columns.Count = 1) Then Exit Function

For i = 1 To rngData.Cells.Count
If rngData.Cells(i).Value <> varValue And _
rngData.Cells(i) <> "" Then
arrData(i) = strDelimiter
Else: arrData(i) = rngData.Cells(i).Value
End If
Next i

varData = Split(Join(arrData, ""), _
strDelimiter, -1, vbTextCompare)

For i = LBound(varData) To UBound(varData)
If varData(i) = Application.Rept(varValue, lngCount) Then _
Consec = Consec + 1
Next i

End Function