Markov Chains

[Omega71]

Hello,

Could someone please explain to me how Markov Chains could be applied to lottery games to predict possible numbers for the next draw. I've been reading a little about Markov Chains but I don't understand how to use them for the lottery. I'm hoping someone could help me understand it in plain simple english.
Thank you to anyone who helps me.

 

[Icewynd]

Hi Omega,

Glad to see someone else is interested in Markov Chains and their application in the lottery.

As summarized by Wikipedia:
"A Markov chain (discrete-time Markov chain or DTMC[1]), named after Andrey Markov, is a mathematical system that undergoes transitions from one state to another on a state space. It is a random process usually characterized as memoryless: the next state depends only on the current state and not on the sequence of events that preceded it. This specific kind of "memorylessness" is called the Markov property."

In lottery, I would say that this means that the numbers in the current draw are only dependent on the numbers from the draw before. For example, if the number in the first (lowest) position is a "3" the number drawn in that position in the next draw will be only dependent on the fact that it was preceded by a 3.

I developed a spreadsheet which counts which numbers follow other numbers. In the case of the 3 in the first position, 44% of the time the next number will be 1-4, especially 2 or 4 at 14% each. More generally, if a 3 is drawn in the lowest position, about 10% of the time another 3 will be drawn and about 70% of the time a number higher than a 3 will be drawn.

Using this method, I can usually get about 2 numbers correct, which is not brilliant! Using groups of 5 (e.g. 1-4, 5-9, 10-14, etc) one can sometimes get 4 or 5 correct, but it gives a lot of numbers to play.

If you have any ideas as to how to fine tune this methodology I would be happy to hear them.

Good luck!

 

[bloubul]

Hi Icewynd

Very interesting theory, I would also like to here more.
Will you share your spreadsheet or the formulas with me.

BlouBul

 

[Icewynd]

Hi Bloubul,

Not complicated at all. The formula is just IF Position 1=1(or whatever) then what is the next number in P1? Then I just do a COUNTIF for each of the possible answers to see what happens most of the time.

Good luck!

 

[Omega71]

Hello Icewynd,

Thank you for the response. I have just started reading about Markov chains so I am not too familiar with them. If possible, could you please give me a copy of your spreadsheet. I have Excel 2007. The spreadsheet would be a great help to me. If not, could you show me the exact formulas you used. All the best to you.

 

[Frank]

I did a similar thing using a macro, however my logic was different. I regard the position of a ball ( after sorting) as an accident dependent on the other balls drawn with it. So I asked the question "which balls followed number N in the following draw regardless of position". For a 6 ball draw this generates 6 balls for each time ball N appears. If ball N was drawn 200 times, then 1200 balls followed it over the lottery history. Within this statistic is a sorted list containing which ball(s) followed it the most. For a lottery with over a thousand draws, I asked myself how far do I really need to look back into history, so my current setting only looks back over the most recent 500 draws.
This is just one of the features I include when creating my challenge numbers.

 

[bloubul]

Frank,

If I may ask will you please share this macro with us, I'm very interested in this theory.

Thanks in Advance

BlouBul

 

[Omega71]

Hello Frank,

Thanks for your input. The most past draws I use is also around 500 because I believe that going further back than that is really pointless. I agree with bloubul, if someone has something that will help others, perhaps it would be nice if they could share it with others. I would be very grateful. Take care.

 

[Frank]

Sharing the macro is not as straightforward as you might think. Its not just a macro, its a fully integrated spreadsheet with over a dozen macros in it, a dozen named ranges, buttons, spinners, input ranges, output ranges, special formatting etc. Plus it only works for a 6/49 lottery.
As has been said before, there are too many ungrateful people here who wish to help themselves to free spreadsheets without a word, even when they haven't a clue or an interest in what it it is doing. That's why I no longer upload spreadsheets, even though it would make my life much easier to do so.
My school motto was "By effort, achieve", which means if you want to attain success, you have to work for it.
I have spent so far, about four hours of my time stripping this spreadsheet back to the bare minumum, so that it does what I described in my post, with some of the bells and whistles removed.
If anyone wishes to share my work then they will need to create their own spreadsheet to my specifications, with named ranges defined by me, and to a layout described by me. Then and only then will the macros (yes there has to be more than one) actually work for a 6/49 lottery excluding bonus..

So who is up for it ? Who is prepared to have a go, it may take a week or two depending on how much spare time I have ? If there is sufficient interest then it may be worth my time.
Its up to you.

 

[Icewynd]

In the spirit of what Frank has proposed, I would suggest "you show me yours and I'll show you mine".

Omega you seem to be an experienced lotto analyst, and I know that Bloubul has developed lottery spreadsheets in the past. Why not show us an idea of yours that will help with lottery analysis. Unless its something totally Mickey Mouse, I will then post my Markov Chain spreadsheet.

Any takers?

Good luck!

 

[bloubul]

Frank

I'm up for it if your offer still stands. "So who is up for it ? Who is prepared to have a go, it may take a week or two depending on how much spare time I have ? If there is sufficient interest then it may be worth my time.
Its up to you.

BlouBul

 

[ChesterDennis]

Don't know much about Markov Chains, but here's another site that may be of interest:

intelbet.somee.com/Default.aspx

CD

 

[Omega71]

Hey Icewynd,

I am not an experienced lotto analyst. I have not been able to come up with anything that works consistently. It seems to me that a new method works only a few times at the beginning and then pretty much fizzles out. The best thing to do, in my opinion, is not rely only on one method. Anyway, I am an experienced Excel user, however, and I do have a spreadsheet with all the previous Ontario Pick 3 draws that I use. I look at patterns such as odd/even, high/low, sums, etc. If I should ever come up with something useful, I would be very happy to share it with everyone. Take care.

 

[Frank]

Well over 300 people have viewed this thread, not many people seem to be foaming at the mouth with excitement over having to create their own Excel 2003 onwards spreadsheet under instruction, so I'll give you a taster of whats in store. I did mine in Excel 2010, but yours has got to be similar.
What follows is just the first sheet and will not produce the finished version. I'm sure it took me a hell of a lot longer to write this than it actually takes to do it ...

Here are two screenshots to help you :-

http://i57.tinypic.com/fp1kz5.jpg
http://i58.tinypic.com/jgsbi9.jpg

And here is what you do:- ..

Select sheet1
On row 4 in cells A4 to G4 type in headings. Draw No. N1 N2 N3 N4 N5 N6 , your results must be in the same column format :- draw number first, then the six main ball numbers (any order) starting on row 6..
Leave row 5 empty this is important. You can format the backgound of A5:G5 in solid grey or whatever colour if you like.

Decide now which 6/49 lottery you will analyse and paste in the results NEWEST result AT THE TOP, on ROW 6.

You will end up with columns A:G from row 6 downwards full of results with draw numbers descending in column A.

Scroll down column A to draw 1, click in the cell directly below the draw number 1, type in the word "start" (press <enter>). Now look up to the top of your spreadsheet to the left of the formula bar .. on the extreme left is the Name box. Type in the word start. (press <enter>) you should now have named the cell you just selected (below cell with draw number 1) "start". You have created a named range called "start" that macros can talk to.

Select cell J2. This cell is for you to enter a number which represents how many draws back from now (and looking forward in time ) you wish to start the analysis. Obviously its a number between the latest draw number and 2. If you enter the latest draw number it does the whole lottery. This type of analysis produces more meaningful results when checking back more than 150 draws because otherwise there will be too many zeroes when asking the question "how many times did number A folllow number B".

I suggest now you enter a value in cell J2 which is more than 150 up to the total number of draws in your table. This sets up the macro analysis scope. If you have 1000 draws in your results table, and you set J2 to 200 then it starts at 200 draws ago and counts 'follow on numbers' forward in time up to the latest draw,.

Important! you need to name cell J2, Give it the name "Drawsback" using the Name box method as above, format a black border around it. Label it in cell J1 "look back".

Another important cell is M1, click on that, format a black border around it, name the cell next_but. Enter a value of Zero in the cell. In cell L1 you can label it Next but.. This value is a kind of skip value, it gives an option of checking not the next draw, but the next but N draw. Leave it zero for normal work.

Cell A2 needs to contain a formula which evaluates the highest numbered draw. In cell A2 enter :- =Offset(A5,1,0). Name this cell "Maxdraw".

The top downwards results list creates an udating problem. What happens in the next draw, where would you put that result ??
Well you create a new row in place of row 6 for you to enter it. However, you wont want to do that manually every time, so you need a macro and a button to do this.

Here is the macro:- which you need to paste in a module ..

Sub NewDraw()


Rows("6:6").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A7:A8").Select
Selection.AutoFill Destination:=Range("A6:A8"), Type:=xlFillDefault


Range("A8:M8").Select
Selection.Copy
Range("A6:M7").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Range("A7:A8").Select
Selection.AutoFill Destination:=Range("A6:A8"), Type:=xlFillDefault


Range("A8:L9").Select
Selection.Copy
Range("A6:L7").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Worksheets("Sheet1").Columns("A:I").AutoFit

Range("A6").Select
End Sub


This can be run from a button, so to create a button, click on the Developer tab, then click on insert, select the first buttton (form control) , your mouse pointer becomes a cross, so you can draw out a square button shape. Immediately a dialog appears asking which macro to link the button to. Ignore what appears at the top, Select Newdraw from the list, click OK.

Right click on the button > select edit text and change the wording on the button to "New draw".
Right click again on the button to resize it or drag it to the top left hand corner to cover up cell A2, which you dont need to see. The macro will read it.

This button (when you click it) should now create new rows as required and enter the next draw number.
If you format row 6 and row 8 and those alternate rows below of your results table with different background colours to the odd numbered rows, the formatting will continue in the new rows when you click to add a new row.

After testing this you can delete the new row(s) by right clicking on the grey row number, and select delete.

Thats all for this part. The next stage will be sheet2 , more named ranges, more buttons and of course the all important macros to do the work!

Good luck!

 

[Omega71]

Hi Frank,

Thank you very much for the first sheet, I'm looking forward to sheet 2. Take care.

 

[bloubul]

Hi Frank

Very impressive.

In column J1 must there be a value in it or not?
If one made a mistake with the Name Range part how do you delete it so that you can re-do it.

BlouBul

 

[Frank]

Thanks Omega71 and Bloubul for responding, that's 2 people I know of who are getting involved.
Regarding range J1 Bloubul, its not for inputting any values, you could leave it blank. The important cell is J2, but you might forget what it is for, so I suggested typing in a label above J2 (which just happens to be J1) to remind you what J2 is for. The word "Lookback" in J1 and perhaps the word "Draws" in J3 might just make sense when there is a number in J2 , for example Lookback 200 draws might be what you would see. Does this make sense?

Regarding deleting named ranges, let me first suggest that your first port of call for any Excel queries is Google. If you type in "delete named range Excel" you get loads of results in less than 5 seconds. I have chosen this one :- http://www.dummies.com/how-to/content/managing-range-names-in-excel-2010.html

You need the Name manager, where it is may vary with your Excel version, but in Excel 2010 it appears on both the insert tab and the formulas tab. All named ranges are listed there and can be selected and deleted. You could also have created the names there had I suggested you do that. You take your choice, whichever is best for you.
Good luck !

 

[Frank]

This next part I tried to post, but the forum rejected it because it was too long!
So I'll have to split up this post.

Its time to create sheet 2, Here are a few screenshots of what we are trying to achieve today.


http://i62.tinypic.com/2nur3ua.jpg
http://i58.tinypic.com/if1wf6.jpg
http://i62.tinypic.com/v6ktv4.jpg


Select sheet2.
Cell C7 is a named range. Name it readout. It is needed as a starting reference for the macro that displays the table of results in a 49 x 49 grid from the search for the next balls drawn after each number.

Cells C6:AY6 are column headers for all 49 ball numbers numbered 1 2 3 ....49. in BOLD font. So cell AY6 should end up with 49 as a header. Cells C7:AY7 are deliberately left blank. This row will come in handy for autofilters later.

Cells B8:B56 are row headers containing all 49 ball numbers in BOLD font:-
1
2
3
4
.
.
.
49, so cell B56 contains 49.

I think its wise to format the row headers and column headers with a background colour to clearly sepatate them from the data within the table. I used grey.


At this point I would select columns B:AY on the grey column labels, right mouse click and choose column width from the menu and set a figure of about 3.5, enough to display 3 digits in whatever font you are using.


Some labelling would not go amiss:-

In cell D4 enter the formula:- =CONCATENATE("Looking back ",drawsback," draws checking the next but ",next_but," draw for follow on numbers, these are the counts")
Increase the font size to make this large and clear.

Highlight cells A21:A42 and click on the "merge and centre button" (its on the home tab). Click in the formula bar and enter this formula:- =CONCATENATE("These numbers followed in the next but ",next_but," draw"). To be able to read it, it needs to be aligned vertically in the merged cells. (its on the alignment tab in the format cells pallette ). This telling you that numbers read off the rows 8 to 56 came second AFTER the numbers read off row 6.

Dont be tempted to do any fancy formatting of the table area at this stage, as it will be wiped when we do conditional formatting. We will be using format painter to copy the conditional formats, overwriting anything we did before.


(continued)

 

[Frank]

continued....
This is awful. My macro is four times longer than the allowable size of a post !!! I'm going to have to chop a macro up ???

All will become clear after we fill the grid with results. Lets do it now.

You need to create a new VB module (do not use the one with the new_draw macro on it.)

Then paste in these macros:-
'-----------------------------------------------------------------------

Option Explicit

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



Sub count_most_followons() 'how many times does a number follow any
' other number in the next or next but one etc (skip m) draw?

Dim i, d, q, m, column As Integer
d = Range("maxdraw").Value 'how many draws
ReDim draws(d) As Integer

clear_arrays

m = Range("next_but").Value + 1 'm is the skip amount between two draws you are comparing,
' e.g. m=1 when you compare one draw with the next to it.

q = Range("drawsback").Value - 1 'how far are you looking back

If q + 1 - m < 1 Then MsgBox ("skip too large for draws back ")

Sheets("sheet1").Select

Application.ScreenUpdating = False

Range("start").Offset(q - d, 0).Select 'Select'Range("start").Select

For column = 0 To 5 'first column is 0 with x direction offset of 0

Selection.Offset(0, 1).Select 'move to right one


For i = 0 To q + 1 - m 'For i = 1 To Range("maxdraw").Value


If Selection.Offset(-i, 0).Value = 1 Then 'we are dealing with N1 ..no.1 and the numbers following it

'whole of following row (all 6 results follow that number)
N1(Selection.Offset(-i - m, 0 - column)) = N1(Selection.Offset(-i - m, 0 - column)) + 1
N1(Selection.Offset(-i - m, 1 - column)) = N1(Selection.Offset(-i - m, 1 - column)) + 1
N1(Selection.Offset(-i - m, 2 - column)) = N1(Selection.Offset(-i - m, 2 - column)) + 1
N1(Selection.Offset(-i - m, 3 - column)) = N1(Selection.Offset(-i - m, 3 - column)) + 1
N1(Selection.Offset(-i - m, 4 - column)) = N1(Selection.Offset(-i - m, 4 - column)) + 1
N1(Selection.Offset(-i - m, 5 - column)) = N1(Selection.Offset(-i - m, 5 - column)) + 1

End If
If Selection.Offset(-i, 0).Value = 2 Then 'we are dealing with N2 ..no.2 and the numbers following it
'whole of following row (all 6 results follow that number)
N2(Selection.Offset(-i - m, 0 - column)) = N2(Selection.Offset(-i - m, 0 - column)) + 1
N2(Selection.Offset(-i - m, 1 - column)) = N2(Selection.Offset(-i - m, 1 - column)) + 1
N2(Selection.Offset(-i - m, 2 - column)) = N2(Selection.Offset(-i - m, 2 - column)) + 1
N2(Selection.Offset(-i - m, 3 - column)) = N2(Selection.Offset(-i - m, 3 - column)) + 1
N2(Selection.Offset(-i - m, 4 - column)) = N2(Selection.Offset(-i - m, 4 - column)) + 1
N2(Selection.Offset(-i - m, 5 - column)) = N2(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 3 Then 'we are dealing with N3 ..no.3 and the numbers following it
'whole of following row (all 6 results follow that number)
N3(Selection.Offset(-i - m, 0 - column)) = N3(Selection.Offset(-i - m, 0 - column)) + 1
N3(Selection.Offset(-i - m, 1 - column)) = N3(Selection.Offset(-i - m, 1 - column)) + 1
N3(Selection.Offset(-i - m, 2 - column)) = N3(Selection.Offset(-i - m, 2 - column)) + 1
N3(Selection.Offset(-i - m, 3 - column)) = N3(Selection.Offset(-i - m, 3 - column)) + 1
N3(Selection.Offset(-i - m, 4 - column)) = N3(Selection.Offset(-i - m, 4 - column)) + 1
N3(Selection.Offset(-i - m, 5 - column)) = N3(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 4 Then 'we are dealing with N4 ..no.4 and the numbers following it
'whole of following row (all 6 results follow that number)
N4(Selection.Offset(-i - m, 0 - column)) = N4(Selection.Offset(-i - m, 0 - column)) + 1
N4(Selection.Offset(-i - m, 1 - column)) = N4(Selection.Offset(-i - m, 1 - column)) + 1
N4(Selection.Offset(-i - m, 2 - column)) = N4(Selection.Offset(-i - m, 2 - column)) + 1
N4(Selection.Offset(-i - m, 3 - column)) = N4(Selection.Offset(-i - m, 3 - column)) + 1
N4(Selection.Offset(-i - m, 4 - column)) = N4(Selection.Offset(-i - m, 4 - column)) + 1
N4(Selection.Offset(-i - m, 5 - column)) = N4(Selection.Offset(-i - m, 5 - column)) + 1

End If

If Selection.Offset(-i, 0).Value = 5 Then 'we are dealing with N5 ..no.5 and the numbers following it
'whole of following row (all 6 results follow that number)
N5(Selection.Offset(-i - m, 0 - column)) = N5(Selection.Offset(-i - m, 0 - column)) + 1
N5(Selection.Offset(-i - m, 1 - column)) = N5(Selection.Offset(-i - m, 1 - column)) + 1
N5(Selection.Offset(-i - m, 2 - column)) = N5(Selection.Offset(-i - m, 2 - column)) + 1
N5(Selection.Offset(-i - m, 3 - column)) = N5(Selection.Offset(-i - m, 3 - column)) + 1
N5(Selection.Offset(-i - m, 4 - column)) = N5(Selection.Offset(-i - m, 4 - column)) + 1
N5(Selection.Offset(-i - m, 5 - column)) = N5(Selection.Offset(-i - m, 5 - column)) + 1

End If


If Selection.Offset(-i, 0).Value = 6 Then 'we are dealing with N6 ..no.6 and the numbers following it
'whole of following row (all 6 results follow that number)
N6(Selection.Offset(-i - m, 0 - column)) = N6(Selection.Offset(-i - m, 0 - column)) + 1
N6(Selection.Offset(-i - m, 1 - column)) = N6(Selection.Offset(-i - m, 1 - column)) + 1
N6(Selection.Offset(-i - m, 2 - column)) = N6(Selection.Offset(-i - m, 2 - column)) + 1
N6(Selection.Offset(-i - m, 3 - column)) = N6(Selection.Offset(-i - m, 3 - column)) + 1
N6(Selection.Offset(-i - m, 4 - column)) = N6(Selection.Offset(-i - m, 4 - column)) + 1
N6(Selection.Offset(-i - m, 5 - column)) = N6(Selection.Offset(-i - m, 5 - column)) + 1

End If


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

End If

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

End If

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

End If



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

End If

(this is not part of the macro ... I'll continue in a new post...)

 

[Frank]

(continued.. join the below on to the above seamlessly....)

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

End If


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

End If

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

End If

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

End If

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

End If

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

End If


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

End If


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

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

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

End If

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

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

End If

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

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

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

End If