Calculating Probability help needed

Hi guys,

This is my first post. Here's what I want to do:

I have 2 numbers, 1 and -1, from A1-A500, just as an example. I want to know what is the probability of a -1 occurring after a 1. Or the probability of a 1 occurring after a -1 or even probability of a 1 occurring after two -1s. I hope I worded my question correctly. I have attachd a file in case someone wants to work with it



Thanks :-)

the following macro will give the probability of -1 following 1. If this is ok can you modify or extend the macro for other possibilities

Sub TEST()
Dim j As Long, k As Long, beg As Range, eend As Range
Set beg = Range("A1")
Set eend = beg.End(xlDown)
j = eend.Row - beg.Row
k = 0
Do
If beg = 1 And beg.Offset(1, 0) - 1 Then k = k + 1
Set beg = beg.Offset(1, 0)
If beg.Offset(1, 0) = "" Then Exit Do
Loop
'MsgBox k
MsgBox "probability of -1 following 1 is   " & k / j
End Sub