Winners & Losers

[gotta know]

Hello,

I have a system that tracks my stock speculation on a spreadsheet that
looks like this:

Row Col A Col H
25 10/1/06 +1.58
26 10/2/06 -
27 10/3/06 -
28 10/4/06 +1.63
29 10/5/06 -0.70
30 10/8/06 -0.05
31 10/9/06 -


Is it possible to track CONSECUTIVE WINS/LOSSES in cell H5? H25:H1000
will either have a PLUS or MINUS figure, or blank.

Thanks in advance.
-Erick

 

[Sandy Mann]

Erick,

If you mean the total of latest run of like gains/losses then try a UDF:

Function CountIt()
Application.Volatile
Dim EndRow As Long
Dim x As Long
Dim Tot As Long

EndRow = Cells(Rows.Count, 8).End(xlUp).Row

If EndRow < 25 Then
MsgBox "No Data"
Exit Function
End If

For x = EndRow To 25 Step -1
If (Cells(x, 8).Value > 0 And Cells(x - 1, 8).Value > 0) Or _
(Cells(x, 8).Value < 0 And Cells(x - 1, 8).Value < 0) Or _
(Cells(x, 8).Value = 0 And Cells(x - 1, 8).Value = 0) Then
Tot = Tot + 1
Else: Exit For
End If
Next x

Tot = Tot + 1

CountIt = Tot

End Function


Application.Sign does not work in VBA - at least in X97


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk