Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consecutive
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consecutive
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 "gotta know" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting consecutive absences | Excel Worksheet Functions | |||
Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
adding three consecutive columns | Excel Worksheet Functions | |||
Return Consecutive Values - Pairs | Excel Worksheet Functions |