Markov Chains

Frank

Frank

Member
(continued, join the below to the above seamlessly)
If Selection.Offset(-i, 0).Value = 26 Then
N26(Selection.Offset(-i - m, 0 - column)) = N26(Selection.Offset(-i - m, 0 - column)) + 1
N26(Selection.Offset(-i - m, 1 - column)) = N26(Selection.Offset(-i - m, 1 - column)) + 1
N26(Selection.Offset(-i - m, 2 - column)) = N26(Selection.Offset(-i - m, 2 - column)) + 1
N26(Selection.Offset(-i - m, 3 - column)) = N26(Selection.Offset(-i - m, 3 - column)) + 1
N26(Selection.Offset(-i - m, 4 - column)) = N26(Selection.Offset(-i - m, 4 - column)) + 1
N26(Selection.Offset(-i - m, 5 - column)) = N26(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 27 Then
N27(Selection.Offset(-i - m, 0 - column)) = N27(Selection.Offset(-i - m, 0 - column)) + 1
N27(Selection.Offset(-i - m, 1 - column)) = N27(Selection.Offset(-i - m, 1 - column)) + 1
N27(Selection.Offset(-i - m, 2 - column)) = N27(Selection.Offset(-i - m, 2 - column)) + 1
N27(Selection.Offset(-i - m, 3 - column)) = N27(Selection.Offset(-i - m, 3 - column)) + 1
N27(Selection.Offset(-i - m, 4 - column)) = N27(Selection.Offset(-i - m, 4 - column)) + 1
N27(Selection.Offset(-i - m, 5 - column)) = N27(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 28 Then
N28(Selection.Offset(-i - m, 0 - column)) = N28(Selection.Offset(-i - m, 0 - column)) + 1
N28(Selection.Offset(-i - m, 1 - column)) = N28(Selection.Offset(-i - m, 1 - column)) + 1
N28(Selection.Offset(-i - m, 2 - column)) = N28(Selection.Offset(-i - m, 2 - column)) + 1
N28(Selection.Offset(-i - m, 3 - column)) = N28(Selection.Offset(-i - m, 3 - column)) + 1
N28(Selection.Offset(-i - m, 4 - column)) = N28(Selection.Offset(-i - m, 4 - column)) + 1
N28(Selection.Offset(-i - m, 5 - column)) = N28(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 29 Then
N29(Selection.Offset(-i - m, 0 - column)) = N29(Selection.Offset(-i - m, 0 - column)) + 1
N29(Selection.Offset(-i - m, 1 - column)) = N29(Selection.Offset(-i - m, 1 - column)) + 1
N29(Selection.Offset(-i - m, 2 - column)) = N29(Selection.Offset(-i - m, 2 - column)) + 1
N29(Selection.Offset(-i - m, 3 - column)) = N29(Selection.Offset(-i - m, 3 - column)) + 1
N29(Selection.Offset(-i - m, 4 - column)) = N29(Selection.Offset(-i - m, 4 - column)) + 1
N29(Selection.Offset(-i - m, 5 - column)) = N29(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 30 Then
N30(Selection.Offset(-i - m, 0 - column)) = N30(Selection.Offset(-i - m, 0 - column)) + 1
N30(Selection.Offset(-i - m, 1 - column)) = N30(Selection.Offset(-i - m, 1 - column)) + 1
N30(Selection.Offset(-i - m, 2 - column)) = N30(Selection.Offset(-i - m, 2 - column)) + 1
N30(Selection.Offset(-i - m, 3 - column)) = N30(Selection.Offset(-i - m, 3 - column)) + 1
N30(Selection.Offset(-i - m, 4 - column)) = N30(Selection.Offset(-i - m, 4 - column)) + 1
N30(Selection.Offset(-i - m, 5 - column)) = N30(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 31 Then
N31(Selection.Offset(-i - m, 0 - column)) = N31(Selection.Offset(-i - m, 0 - column)) + 1
N31(Selection.Offset(-i - m, 1 - column)) = N31(Selection.Offset(-i - m, 1 - column)) + 1
N31(Selection.Offset(-i - m, 2 - column)) = N31(Selection.Offset(-i - m, 2 - column)) + 1
N31(Selection.Offset(-i - m, 3 - column)) = N31(Selection.Offset(-i - m, 3 - column)) + 1
N31(Selection.Offset(-i - m, 4 - column)) = N31(Selection.Offset(-i - m, 4 - column)) + 1
N31(Selection.Offset(-i - m, 5 - column)) = N31(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 32 Then
N32(Selection.Offset(-i - m, 0 - column)) = N32(Selection.Offset(-i - m, 0 - column)) + 1
N32(Selection.Offset(-i - m, 1 - column)) = N32(Selection.Offset(-i - m, 1 - column)) + 1
N32(Selection.Offset(-i - m, 2 - column)) = N32(Selection.Offset(-i - m, 2 - column)) + 1
N32(Selection.Offset(-i - m, 3 - column)) = N32(Selection.Offset(-i - m, 3 - column)) + 1
N32(Selection.Offset(-i - m, 4 - column)) = N32(Selection.Offset(-i - m, 4 - column)) + 1
N32(Selection.Offset(-i - m, 5 - column)) = N32(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 33 Then
N33(Selection.Offset(-i - m, 0 - column)) = N33(Selection.Offset(-i - m, 0 - column)) + 1
N33(Selection.Offset(-i - m, 1 - column)) = N33(Selection.Offset(-i - m, 1 - column)) + 1
N33(Selection.Offset(-i - m, 2 - column)) = N33(Selection.Offset(-i - m, 2 - column)) + 1
N33(Selection.Offset(-i - m, 3 - column)) = N33(Selection.Offset(-i - m, 3 - column)) + 1
N33(Selection.Offset(-i - m, 4 - column)) = N33(Selection.Offset(-i - m, 4 - column)) + 1
N33(Selection.Offset(-i - m, 5 - column)) = N33(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 34 Then
N34(Selection.Offset(-i - m, 0 - column)) = N34(Selection.Offset(-i - m, 0 - column)) + 1
N34(Selection.Offset(-i - m, 1 - column)) = N34(Selection.Offset(-i - m, 1 - column)) + 1
N34(Selection.Offset(-i - m, 2 - column)) = N34(Selection.Offset(-i - m, 2 - column)) + 1
N34(Selection.Offset(-i - m, 3 - column)) = N34(Selection.Offset(-i - m, 3 - column)) + 1
N34(Selection.Offset(-i - m, 4 - column)) = N34(Selection.Offset(-i - m, 4 - column)) + 1
N34(Selection.Offset(-i - m, 5 - column)) = N34(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 35 Then
N35(Selection.Offset(-i - m, 0 - column)) = N35(Selection.Offset(-i - m, 0 - column)) + 1
N35(Selection.Offset(-i - m, 1 - column)) = N35(Selection.Offset(-i - m, 1 - column)) + 1
N35(Selection.Offset(-i - m, 2 - column)) = N35(Selection.Offset(-i - m, 2 - column)) + 1
N35(Selection.Offset(-i - m, 3 - column)) = N35(Selection.Offset(-i - m, 3 - column)) + 1
N35(Selection.Offset(-i - m, 4 - column)) = N35(Selection.Offset(-i - m, 4 - column)) + 1
N35(Selection.Offset(-i - m, 5 - column)) = N35(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 36 Then
N36(Selection.Offset(-i - m, 0 - column)) = N36(Selection.Offset(-i - m, 0 - column)) + 1
N36(Selection.Offset(-i - m, 1 - column)) = N36(Selection.Offset(-i - m, 1 - column)) + 1
N36(Selection.Offset(-i - m, 2 - column)) = N36(Selection.Offset(-i - m, 2 - column)) + 1
N36(Selection.Offset(-i - m, 3 - column)) = N36(Selection.Offset(-i - m, 3 - column)) + 1
N36(Selection.Offset(-i - m, 4 - column)) = N36(Selection.Offset(-i - m, 4 - column)) + 1
N36(Selection.Offset(-i - m, 5 - column)) = N36(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 37 Then
N37(Selection.Offset(-i - m, 0 - column)) = N37(Selection.Offset(-i - m, 0 - column)) + 1
N37(Selection.Offset(-i - m, 1 - column)) = N37(Selection.Offset(-i - m, 1 - column)) + 1
N37(Selection.Offset(-i - m, 2 - column)) = N37(Selection.Offset(-i - m, 2 - column)) + 1
N37(Selection.Offset(-i - m, 3 - column)) = N37(Selection.Offset(-i - m, 3 - column)) + 1
N37(Selection.Offset(-i - m, 4 - column)) = N37(Selection.Offset(-i - m, 4 - column)) + 1
N37(Selection.Offset(-i - m, 5 - column)) = N37(Selection.Offset(-i - m, 5 - column)) + 1
End If
 
Frank

Frank

Member
(continued. join this seamlessly to the above)

If Selection.Offset(-i, 0).Value = 38 Then
N38(Selection.Offset(-i - m, 0 - column)) = N38(Selection.Offset(-i - m, 0 - column)) + 1
N38(Selection.Offset(-i - m, 1 - column)) = N38(Selection.Offset(-i - m, 1 - column)) + 1
N38(Selection.Offset(-i - m, 2 - column)) = N38(Selection.Offset(-i - m, 2 - column)) + 1
N38(Selection.Offset(-i - m, 3 - column)) = N38(Selection.Offset(-i - m, 3 - column)) + 1
N38(Selection.Offset(-i - m, 4 - column)) = N38(Selection.Offset(-i - m, 4 - column)) + 1
N38(Selection.Offset(-i - m, 5 - column)) = N38(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 39 Then
N39(Selection.Offset(-i - m, 0 - column)) = N39(Selection.Offset(-i - m, 0 - column)) + 1
N39(Selection.Offset(-i - m, 1 - column)) = N39(Selection.Offset(-i - m, 1 - column)) + 1
N39(Selection.Offset(-i - m, 2 - column)) = N39(Selection.Offset(-i - m, 2 - column)) + 1
N39(Selection.Offset(-i - m, 3 - column)) = N39(Selection.Offset(-i - m, 3 - column)) + 1
N39(Selection.Offset(-i - m, 4 - column)) = N39(Selection.Offset(-i - m, 4 - column)) + 1
N39(Selection.Offset(-i - m, 5 - column)) = N39(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 40 Then
N40(Selection.Offset(-i - m, 0 - column)) = N40(Selection.Offset(-i - m, 0 - column)) + 1
N40(Selection.Offset(-i - m, 1 - column)) = N40(Selection.Offset(-i - m, 1 - column)) + 1
N40(Selection.Offset(-i - m, 2 - column)) = N40(Selection.Offset(-i - m, 2 - column)) + 1
N40(Selection.Offset(-i - m, 3 - column)) = N40(Selection.Offset(-i - m, 3 - column)) + 1
N40(Selection.Offset(-i - m, 4 - column)) = N40(Selection.Offset(-i - m, 4 - column)) + 1
N40(Selection.Offset(-i - m, 5 - column)) = N40(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 41 Then
N41(Selection.Offset(-i - m, 0 - column)) = N41(Selection.Offset(-i - m, 0 - column)) + 1
N41(Selection.Offset(-i - m, 1 - column)) = N41(Selection.Offset(-i - m, 1 - column)) + 1
N41(Selection.Offset(-i - m, 2 - column)) = N41(Selection.Offset(-i - m, 2 - column)) + 1
N41(Selection.Offset(-i - m, 3 - column)) = N41(Selection.Offset(-i - m, 3 - column)) + 1
N41(Selection.Offset(-i - m, 4 - column)) = N41(Selection.Offset(-i - m, 4 - column)) + 1
N41(Selection.Offset(-i - m, 5 - column)) = N41(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 42 Then
N42(Selection.Offset(-i - m, 0 - column)) = N42(Selection.Offset(-i - m, 0 - column)) + 1
N42(Selection.Offset(-i - m, 1 - column)) = N42(Selection.Offset(-i - m, 1 - column)) + 1
N42(Selection.Offset(-i - m, 2 - column)) = N42(Selection.Offset(-i - m, 2 - column)) + 1
N42(Selection.Offset(-i - m, 3 - column)) = N42(Selection.Offset(-i - m, 3 - column)) + 1
N42(Selection.Offset(-i - m, 4 - column)) = N42(Selection.Offset(-i - m, 4 - column)) + 1
N42(Selection.Offset(-i - m, 5 - column)) = N42(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 43 Then
N43(Selection.Offset(-i - m, 0 - column)) = N43(Selection.Offset(-i - m, 0 - column)) + 1
N43(Selection.Offset(-i - m, 1 - column)) = N43(Selection.Offset(-i - m, 1 - column)) + 1
N43(Selection.Offset(-i - m, 2 - column)) = N43(Selection.Offset(-i - m, 2 - column)) + 1
N43(Selection.Offset(-i - m, 3 - column)) = N43(Selection.Offset(-i - m, 3 - column)) + 1
N43(Selection.Offset(-i - m, 4 - column)) = N43(Selection.Offset(-i - m, 4 - column)) + 1
N43(Selection.Offset(-i - m, 5 - column)) = N43(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 44 Then
N44(Selection.Offset(-i - m, 0 - column)) = N44(Selection.Offset(-i - m, 0 - column)) + 1
N44(Selection.Offset(-i - m, 1 - column)) = N44(Selection.Offset(-i - m, 1 - column)) + 1
N44(Selection.Offset(-i - m, 2 - column)) = N44(Selection.Offset(-i - m, 2 - column)) + 1
N44(Selection.Offset(-i - m, 3 - column)) = N44(Selection.Offset(-i - m, 3 - column)) + 1
N44(Selection.Offset(-i - m, 4 - column)) = N44(Selection.Offset(-i - m, 4 - column)) + 1
N44(Selection.Offset(-i - m, 5 - column)) = N44(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 45 Then
N45(Selection.Offset(-i - m, 0 - column)) = N45(Selection.Offset(-i - m, 0 - column)) + 1
N45(Selection.Offset(-i - m, 1 - column)) = N45(Selection.Offset(-i - m, 1 - column)) + 1
N45(Selection.Offset(-i - m, 2 - column)) = N45(Selection.Offset(-i - m, 2 - column)) + 1
N45(Selection.Offset(-i - m, 3 - column)) = N45(Selection.Offset(-i - m, 3 - column)) + 1
N45(Selection.Offset(-i - m, 4 - column)) = N45(Selection.Offset(-i - m, 4 - column)) + 1
N45(Selection.Offset(-i - m, 5 - column)) = N45(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 46 Then
N46(Selection.Offset(-i - m, 0 - column)) = N46(Selection.Offset(-i - m, 0 - column)) + 1
N46(Selection.Offset(-i - m, 1 - column)) = N46(Selection.Offset(-i - m, 1 - column)) + 1
N46(Selection.Offset(-i - m, 2 - column)) = N46(Selection.Offset(-i - m, 2 - column)) + 1
N46(Selection.Offset(-i - m, 3 - column)) = N46(Selection.Offset(-i - m, 3 - column)) + 1
N46(Selection.Offset(-i - m, 4 - column)) = N46(Selection.Offset(-i - m, 4 - column)) + 1
N46(Selection.Offset(-i - m, 5 - column)) = N46(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 47 Then
N47(Selection.Offset(-i - m, 0 - column)) = N47(Selection.Offset(-i - m, 0 - column)) + 1
N47(Selection.Offset(-i - m, 1 - column)) = N47(Selection.Offset(-i - m, 1 - column)) + 1
N47(Selection.Offset(-i - m, 2 - column)) = N47(Selection.Offset(-i - m, 2 - column)) + 1
N47(Selection.Offset(-i - m, 3 - column)) = N47(Selection.Offset(-i - m, 3 - column)) + 1
N47(Selection.Offset(-i - m, 4 - column)) = N47(Selection.Offset(-i - m, 4 - column)) + 1
N47(Selection.Offset(-i - m, 5 - column)) = N47(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 48 Then
N48(Selection.Offset(-i - m, 0 - column)) = N48(Selection.Offset(-i - m, 0 - column)) + 1
N48(Selection.Offset(-i - m, 1 - column)) = N48(Selection.Offset(-i - m, 1 - column)) + 1
N48(Selection.Offset(-i - m, 2 - column)) = N48(Selection.Offset(-i - m, 2 - column)) + 1
N48(Selection.Offset(-i - m, 3 - column)) = N48(Selection.Offset(-i - m, 3 - column)) + 1
N48(Selection.Offset(-i - m, 4 - column)) = N48(Selection.Offset(-i - m, 4 - column)) + 1
N48(Selection.Offset(-i - m, 5 - column)) = N48(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 49 Then
N49(Selection.Offset(-i - m, 0 - column)) = N49(Selection.Offset(-i - m, 0 - column)) + 1
N49(Selection.Offset(-i - m, 1 - column)) = N49(Selection.Offset(-i - m, 1 - column)) + 1
N49(Selection.Offset(-i - m, 2 - column)) = N49(Selection.Offset(-i - m, 2 - column)) + 1
N49(Selection.Offset(-i - m, 3 - column)) = N49(Selection.Offset(-i - m, 3 - column)) + 1
N49(Selection.Offset(-i - m, 4 - column)) = N49(Selection.Offset(-i - m, 4 - column)) + 1
N49(Selection.Offset(-i - m, 5 - column)) = N49(Selection.Offset(-i - m, 5 - column)) + 1

End If



Next i
Next column
Application.ScreenUpdating = True
readout
'CreateMostLikelyList
End Sub
 
Frank

Frank

Member
Thats one macro done, now for some more, followed by some further work:-

Still on the same module as the long macro above...



Sub clear_arrays()
Dim i As Integer
For i = 1 To 49
N1(i) = 0
N2(i) = 0
N3(i) = 0
N4(i) = 0
N5(i) = 0
N6(i) = 0
N7(i) = 0
N8(i) = 0
N9(i) = 0
N10(i) = 0
N11(i) = 0
N12(i) = 0
N13(i) = 0
N14(i) = 0
N15(i) = 0
N16(i) = 0
N17(i) = 0
N18(i) = 0
N19(i) = 0
N20(i) = 0
N21(i) = 0
N22(i) = 0
N23(i) = 0
N24(i) = 0
N25(i) = 0
N26(i) = 0
N27(i) = 0
N28(i) = 0
N29(i) = 0
N30(i) = 0
N31(i) = 0
N32(i) = 0
N33(i) = 0
N34(i) = 0
N35(i) = 0
N36(i) = 0
N37(i) = 0
N38(i) = 0
N39(i) = 0
N40(i) = 0
N41(i) = 0
N42(i) = 0
N43(i) = 0
N44(i) = 0
N45(i) = 0
N46(i) = 0
N47(i) = 0
N48(i) = 0
N49(i) = 0
Next
End Sub

Sub readout()
Dim j As Integer

Sheets("sheet2").Select
Range("readout").Select
Application.Calculation = xlCalculationManual
For j = 1 To 49

Selection.Offset(j, 0) = N1(j)
Selection.Offset(j, 1) = N2(j)
Selection.Offset(j, 2) = N3(j)
Selection.Offset(j, 3) = N4(j)
Selection.Offset(j, 4) = N5(j)

Selection.Offset(j, 5) = N6(j)
Selection.Offset(j, 6) = N7(j)
Selection.Offset(j, 7) = N8(j)
Selection.Offset(j, 8) = N9(j)

Selection.Offset(j, 9) = N10(j)
Selection.Offset(j, 10) = N11(j)
Selection.Offset(j, 11) = N12(j)
Selection.Offset(j, 12) = N13(j)
Selection.Offset(j, 13) = N14(j)

Selection.Offset(j, 14) = N15(j)
Selection.Offset(j, 15) = N16(j)
Selection.Offset(j, 16) = N17(j)
Selection.Offset(j, 17) = N18(j)
Selection.Offset(j, 18) = N19(j)

Selection.Offset(j, 19) = N20(j)
Selection.Offset(j, 20) = N21(j)
Selection.Offset(j, 21) = N22(j)
Selection.Offset(j, 22) = N23(j)
Selection.Offset(j, 23) = N24(j)

Selection.Offset(j, 24) = N25(j)
Selection.Offset(j, 25) = N26(j)
Selection.Offset(j, 26) = N27(j)
Selection.Offset(j, 27) = N28(j)
Selection.Offset(j, 28) = N29(j)

Selection.Offset(j, 29) = N30(j)
Selection.Offset(j, 30) = N31(j)
Selection.Offset(j, 31) = N32(j)
Selection.Offset(j, 32) = N33(j)
Selection.Offset(j, 33) = N34(j)

Selection.Offset(j, 34) = N35(j)
Selection.Offset(j, 35) = N36(j)
Selection.Offset(j, 36) = N37(j)
Selection.Offset(j, 37) = N38(j)
Selection.Offset(j, 38) = N39(j)


Selection.Offset(j, 39) = N40(j)
Selection.Offset(j, 40) = N41(j)
Selection.Offset(j, 41) = N42(j)
Selection.Offset(j, 42) = N43(j)
Selection.Offset(j, 43) = N44(j)

Selection.Offset(j, 44) = N45(j)
Selection.Offset(j, 45) = N46(j)
Selection.Offset(j, 46) = N47(j)
Selection.Offset(j, 47) = N48(j)
Selection.Offset(j, 48) = N49(j)



Next

Application.Calculation = xlCalculationAutomatic
End Sub

'-----------------------------------------------------------------------------------
thats the end of the macros.

There is more than one macro here and the macro count_most_followons() is the main one, it runs the the other macros within it. So a button would be good here linked to the macro count_most_followons. I suggest you creat a forms button on Sheet 1 in the same way as the new draw button and link it to count_most_followons. A good place to locate it is beside the new draw button over columns B:G as in the screenshot.

Before you test this macro:-
Make sure you are on sheet1, check you have a zero in cell M1, check you have set a high value in cell J2, at least half way back in lottery history. Now you can click it and see it fills up the table on sheet2. We need a few more things to help us on sheet2..


1. Checksums. Its important to know that all the data requested has been harvested and there is no missing data. so we need to count all the appearances in the table. In cell C58 enter the formula =SUM(C8:C56). You then need to copy this formula (Drag the fill handle in C58) across row 58 through all cells to cell AY58. So we've added up all column appearances. Now we need to add these totals up too to get a grand total. In cell AZ58 enter the formula =SUM(C58:AY58). Is this total correct? Well to find out we need to work out 36 x number of draw comparisons made, since 6 balls follow each of the 6 balls in each draw, making 36.

in cell BA58 enter the formula =(Drawsback-1-next_but)*36. Now cell AZ58 should equal cell BA58.

Now test the macro again, but set cell J2 to 2. Only one draw will be compared to the most recent one and you would expect only 6 balls to be found following each ball number from one draw ago. The totals in 6 of the columns will total 6 and the totals in BA58 and AZ58 will be 36. The rest of the table will be full of zeroes. Now the table is nearly ready for use for real.

So how do you read this table ? Choose any number from row 6 that has actually been drawn. in the column below it is a list of the counts (not ball numbers) of times when other numbers followed it at the set skip (M1). The identity of the numbers are read off on the row where the count value occurs in column B.

2. Maximum and minimum. Its handy to know the maximum and minimum count values in each column, so that they can be used in conjunction with conditional formatting to highlight max and min values in the table.

On sheet2, in cell C1 enter the formula =MIN(C8:C56) and copy it across all cells to AY1.
in cell C2 enter the formula =MAX(C8:C56) and copy it across all cells to AY2.
Maybe a bit of background colour and format a grid of thin borders around these 2 rows of info will make it clearer. Cell B1 can contain "min" as a row header, and cell C2 can contain "max" as a row header.


3.Now for the hard bit. Conditional formatting - this is optional, you can skip it but I think its really useful. This may depend on your version of Excel and can be really tricky!
We wish to make maximum counts in each column glow red, minima counts in each column glow blue.

In Excel 2010:-
you would click on cell C8 then select conditional formatting.>New rule.
select "use a formula to determine which cells to format".

in the formula box enter =C8=C$1 click the format button and choose (say) blue Bold Font style. Click OK. Then click Ok to leave the dialog.

Whilst still in cell C8 select conditional formatting.>New rule
select "use a formula to determine which cells to format"
in the box enter =c8=C$2 click the format button and choose (say) Red Bold Font style. Click OK. Then Ok to leave the dialog.

That's just one cell set up with conditional formatting, but because the formula I created is movable (no dollar signs used for C8) we can use format painter to apply these rules to the whole table, whilst still comparing with the correct max and min values.

Click on C8, click on the format painter button (home tab) and select D8:AY8. This should set up conditional formatting on the rest of that row.
Now select C8:AY8, click on format painter and then select C9:AY:56. The whole table should now show max and min values in colour. Thats conditional formatting completed.

Now we can format the table background of alternate rows of the table white and another colour, I've used pale orange on even rows. You only need to do the top 2 rows , select them and use format painter to copy the pattern down the table. Finally select the whole table C8:AY8 and do a thick box black outline border.

Optional:-, If you want to know how many times a number followed itself at the set skip, these counts are listed on the diagonal cells of the table. So I highlighted the background colour of the diagonal cells c8,D9,E10,F11,.......AY56 in pale yellow.

PHEW ! Thats all for now, there is another couple of useful macros if you want them. Also the input cells J2,M1 could be driven by spinners..... BUT You need to get this working first !

P.S
If you get it working, try selecting C7:AY7 , go to the Data tab, select filter. Now pick a ball number who you wish to study, look down the column and note the maximum value shown in red, select the filter above that column, uncheck "select all" and check the maximum value you just saw, this will create a list on the left of the ball numbers having that maximum value. Don't forget to reset the filter back again to select all when you've done.
That's quite enough for now.

:goodluck:

Good Luck!

PS this forums posting limits made my job here 10 times harder, lots of scope for mistakes in continuity of macros !!!
 
bloubul

bloubul

Member
Hi Frank

Thank you for all this work. Please upload the screen dumbs on mediafire I cannot access it with your link

BlouBul :cool:
 
Frank

Frank

Member
Hi Bloubul, It may be a tinypic restriction by country.
Try this for the screenshots:-

http://www.mediafire.com/download/e2k5zzvheiuk6jg/project.rar
 
C

cdrake

Member
Isn't this similar to the announcers method. In lottostatistics.xls you can put in a number or a group of numbers for that matter and see what other numbers came up with, before, or after the said group. I used a small dos utility that produced Markov chains for the lotteries years ago but haven't used it in a long time. It was rather cumbersome as you had to go into the command prompt to run it for each cycle. From what I remembered most of the combinations at the front of the file were heavily weighted to those numbers that came out in the recent draws. Sometimes you'd get a few 3 number hits while most of the time it would be a bust.
 
Frank

Frank

Member
It sounds like something similar, though I'm not familiar with that piece of software. It sounds like it might have been written in C and an exe file created from that. It would certainly run faster than VBA.
I just thought it might be useful to tabulate all numbers following all numbers, building in a variable skip. I ended up with this Excel version from which you can choose to look for most numbers coming next, or least numbers coming next, or anything in between on a ball by ball basis. :)
 
bloubul

bloubul

Member
Hi Frank

Cdrake is talking about a spreadsheet that was done by Nick Koutras.
Well he does not partake on the forum any more........????? Who knows..
If you would I can sent you a copy.

BlouBul :cool:
 
Frank

Frank

Member
Thanks Bloubul, I'm not bothered either way, I prefer my own spreadsheets, at least I know how to use them. The only Nick Koutras spreadsheets I had, I could not use as there were no instructions, and I couldn't figure them out so I deleted them and wrote my own. :rolling:
 
bloubul

bloubul

Member
Hi Frank

Please help me. I'm getting the following errors when I try to run the "Count Follow ons" macro.

If I change J2(sheet1) to 2 as per your writing the following error:

"Run-Time error '1004':
Application-definded or object-defined error.
Than it highlights this:
"Range("start").Offset(q - d, 0).Select 'Select'Range("start").Select"

If I change it back to 200 the same run time error but with this highlighted:
"If Selection.Offset(-i, 0).Value = 1 Then 'we are dealing with N1 ..no.1 and the numbers following it"

I have followed your instructions to the letter.


BlouBul :cool:
 
Frank

Frank

Member
Hi Bloubul. If you are testing a 6/49 lottery, (which is all its designed for) I can only think it to be down to some structural difference in your spreadsheet layout. The macros are well established and proven when run in a spreadsheet with my specification. I rebuilt the spreadsheet from scratch following my own instructions and it works perfectly.

How many draws have you got in your results ?
What is the address of the range you have named as "start" ? It should be six rows more than the highest draw number.

example 1987 draws, range("start") is cell A1993. If you have 1000 draws, range("start") is cell A1006.

Are cells A5:G5 empty as they should be ?

Check your five named ranges as follows:- On sheet1 move your mouse pointer to the Name box, select the dropdown and select each named range (in turn) from the dropdown, make sure the sheet jumps to the correct cell address.

Check the insert new draw button, does the new draw number remain firmly on row 6?

Apart from this, as I cannot see your sheet, its hard for me to know whats going wrong.

If you cant solve it after the above checks, e-mail it to me, I'll take a look. :)

Omega71, can I assume that it worked OK for you, you found no problems creating it ?
 
S

stoopendaal

Member
Website Markov Chains following draw

Hi,

An online version of creating Markov Chains for your lottery game can be found here: http://intelbet.somee.com/

Also I added Frank's function 'which balls followed number N in the following draw regardless of position' to this website. Here you have also the option to look for most numbers coming next, least number coming next and the current skips (relative) it has with the numbers.
Screenshot: http://intelbet.somee.com/Images/MCNEXT.png

Good Luck!
 
Frank

Frank

Member
Thanks Stoopendal for the on line link, useful for non Excel users.

Bloubul,
As I explained in my e-mail after examining your spreadsheet, you did not carry out my instructions to the letter, there were several mistakes and omissions which I have corrected for you.

However, the main thing that killed the macro was your use of an incomplete results set which had a chunk of about 60 draws missing in the middle. My macro was expecting to deal with a number of draws equal to the highest draw number, but because there were actually less - it crashed out.
In order to prevent this from happening in the future, I have changed the formula in cell A2 to =COUNTIF(A5:A10000,">0") which assumes no results list will be longer than 10000 draws. Then it will deal with the ACTUAL number of results rather than trying to access a complete set of results up to the highest draw number.
You should find that the macro now works ... :)

If you wish to change the lottery data (and hence the number of draws) I recommend you use a different copy of the sheet rather than amend this one. It is important at all times to keep the named range "start" immediately below draw number 1.
 
Frank

Frank

Member
Well I appreciate your willingness to try and create this project, and learn something along the way. There are no short cuts to learning Excel skills. Practice makes perfect. Had I just uploaded it, 150 nameless people would have helped themselves and learned nothing. :)

The table of follow on numbers that this creates is just the beginning. Other macros can work on this table and give us a summary table of ball numbers which followed on most, (could be amended to least). This does a similar job to filtering but shows all the most common follow on balls at the same time. Another macro can answer the question " when did ball A follow ball B"? by listing draw numbers.
If there is any interest in these please let me know.
 
bloubul

bloubul

Member
Hi Frank

I'm interested because I want to learn, I have made mistakes yes, but I will pay more attention this time.

BlouBul :cool:
 
Frank

Frank

Member
No problem Bloubul, I appreciate that my instructions are long and wordy, its easy to get on the wrong line. Theres no short cut to giving detailed instructions either, unfortunately.
Another good improvement to the macros is to the new draw button. I realise that one might forget about this spreadsheet for a year or so, come back and think Oh now Ive got to add 100 lines to the top to fit in all the new draws. You can keep clicking of course, but one slight modification and you can set it to add as many as you like at once... I'll be back in a day or so with more...
 
Frank

Frank

Member
Part 3.

Heres a screenshot sheet2 lower half
http://www.mediafire.com/view/760l8x7pqdd9p98/project5.jpg

Ok on sheet 2 we are going to create 2 identically shaped tables, one summarising the ball numbers whose counts following a number equal maximum values, the other summarising the ball numbers whose counts following a number equal minumum values.
The good news is we only need to to do one table, :lphant: then we can copy and paste it, only the table header and colour and border need be different. So the copy we make can be edited to suit.

To create a maximum summary table, first select range C62:AY78 and put an outline thick box border around it.

Cells C63:AY63 are column headers listing all 49 balls:- C63 is number 1... right through to AY63 with 49 in it.

Row 62 of this table is the Table header identifying what the table is all about. In cell E62 I had entered some text in bold font "Listing Of Maxima".

Also in Cell O62 I had typed in a formula =CONCATENATE("THESE ARE THE BALLS WHICH HAVE FOLLOWED EACH OTHER NUMBER MOST IN THE NEXT BUT N DRAW WHERE N= ",next_but)


Now it really helps not to show zero values in this table, so special custom formatting is required.
Select range C64:AY78 (this is where the data will be) . Bring up the format cells dialog from your home tab (or shortcut Ctrl + shift + F), click on the number tab, click on the custom category,click on anything in the right hand listing so it appears in the typing box. You need to delete the existing codes in there and replace with this :- 0;-0;;@ Click OK. This suppresses all zeroes in your new table.


On row 79 we could do with a reminder of what the maximum values are, so in cell C79 enter the formula =C2 then copy this formula along the row to cell AY79.

Finally, we will create a sideways label similar to the one we did earlier in the main 49 x49 table. Select the range B65:B74 , click on the merge and centre button, click in the formula bar and enter this formula =CONCATENATE("lookback ",Drawsback," draws").
Use format painter to copy the vertical format of the main 49x49 label (merged cell A21) to our new tall merged cell B65 .
Thats the structure of the maximum table done (apart from formatting).

To make the minima table we copy this whole table. So select range B62:AY79 then click in cell B82 and Paste it there. We now have a
minima table that needs editing to match its new role.

Cell E82 now should read Listing Of Minima

The minimum formula now in cell C99 needs to be edited to read =C1 and copied across to the others on row 99 to cell AY99.


To make the tables look different, apply different colour background formatting to the two header rows, maybe change the colour of the table
borders, add row labels in column A for the max and min values on rows 79 and 99. Also in column A, you might add a row header in a double height merged cell "follows ball number" opposite the table headers. e.g cell A62 is 2 merged cells.



Now for six more Named ranges:-

On sheet 2 select range C64:AY78 and Name the selected range maxtable. Just type it into the Name box and press enter whist its selected.
Alternatively use the Name Manager from the formulas menu.

Click on cell B63 name it maxlist.

On sheet 2 select range C84:AY98 and Name the selected range mintable. Just type it into the Name box and press enter whist its selected.
Alternatively use the Name Manager from the formulas menu.

click on cell B83 name it minlist.

click on Cell B1 and name it min

click on cell B2 and name it max

End of naming ranges ........................

Now for 2 macros to fill these tables:-

On the same module as the one you used for Sub count_most_followons() and its associated macros add these macros:-

Sub CreateMaxList()
Dim i, j, count As Integer
Dim maxima(49, 49) As Integer 'the balls which are equal to the max in the column
Dim flag As Boolean
flag = False

Range("readout").Select
For j = 1 To 49 ' ballnumber across left to right

count = 0
For i = 1 To 49 ' result in column

If ActiveCell.Offset(i, j - 1) = Range("max").Offset(0, j) Then
count = count + 1
If count > 15 Then flag = True

maxima(j, count) = ActiveCell.Offset(i, -1).Value
Debug.Print "j= "; j, "max = "; Range("max").Offset(0, j), "count= ", ; count, "number= ", maxima(j, count)


End If

Next i


Next j

'READOUT
Range("maxtable").ClearContents
Application.Calculation = xlCalculationManual

Range("maxlist").Select
For i = 1 To 15 ' 15 (count value) being max number of possible numbers having that maxima
For j = 1 To 49
If Range("max").Offset(0, j) = 0 Then
ActiveCell.Offset(i, j) = 0
Else
ActiveCell.Offset(i, j) = maxima(j, i)
End If
Next
Next
If flag = True Then

MsgBox "Listing of maxima has Too many equal results. You need to look back more draws to reduce them"

End If
Application.Calculation = xlCalculationAutomatic

End Sub




Sub CreateMinList()
Dim i, j, count As Integer
Dim minima(49, 49) As Integer 'the balls which are equal to the max in the column
Dim flag As Boolean
flag = False

Range("readout").Select
For j = 1 To 49 ' ballnumber across left to right

count = 0
For i = 1 To 49 ' result in column

If ActiveCell.Offset(i, j - 1) = Range("min").Offset(0, j) Then
count = count + 1
If count > 15 Then flag = True

minima(j, count) = ActiveCell.Offset(i, -1).Value
Debug.Print "j= "; j, "min = "; Range("min").Offset(0, j), "count= ", ; count, "number= ", minima(j, count)


End If

Next i

Next j

'READOUT
Range("mintable").ClearContents
Application.Calculation = xlCalculationManual

Range("minlist").Select
For i = 1 To 15 ' 15 (count value) being max number of possible numbers having that maxima
For j = 1 To 49
If Range("min").Offset(0, j) = 0 Then
ActiveCell.Offset(i, j) = 0
Else
ActiveCell.Offset(i, j) = minima(j, i)
End If
Next
Next
If flag = True Then

MsgBox "Listing of minima has Too many equal results. You need to look back more draws to reduce them"

End If
Application.Calculation = xlCalculationAutomatic

End Sub

'-----------------------------------------------

These macros at the moment will not run as they need to be called upon by the main macro count_most_followons(), so you need to find that macro, scroll to the very end of it , just before End sub. You will see the macro ends with ...

Application.ScreenUpdating = True
readout
'CreateMostLikelyList
End Sub


You need to edit the macro by deleting the line 'CreateMostLikelyList

and replace it with 2 lines :-

CreateMaxList
CreateMinList

so it looks like:-

Application.ScreenUpdating = True
readout
CreateMaxList
CreateMinList
End Sub

Thats it.. Stage three completed. You should have a total of 11 named ranges now on your spreadsheet. If you haven't there will be errors when the macros are run from the button on sheet1.
If it works, you should now have 3 tables of information on sheet2. The bottom 2 tables contain vertical lists of Ball numbers corresponding to either the maximum count or minimum count for ball B (inside the table) following Ball A chosen along the horizontal headers.
For a low lookback setting, the max min tables will have too many equal max or min values to display, so a message will appear, but the macros will still run.


All that remains are optional extras. A macro to display when ball B followed ball A (draw numbers), or when a ball followed itself (repeated), you supply the input values for balls A and B.

Also Some spinners and a modified New draw macro would be nice. Thats in the final part.

Good luck!
 
You must log in or register to reply here.

Sidebar

Top