Macro to find Fibonacci patterns or number sequences
- Thread starter larbec
- Start date
Frank
Member
Why do you specify a macro? This can be achieved with formulas either adjacent to the range or as a conditional formatting command to highlight Fibbonaci numbers in situ in the original data range.
Example:-
copy this formula to your clipboard first. =OR(A3=1,A3=2,A3=5,A3=8,A3=13,A3=21,A3=34,A3=55,A3=55,A3=89)
click on cell A3
go to Home tab >>> conditional formatting.
Highlight cell rules >>> more rules
choose use a formula to determine which cells to format
in the box "format values where this formula is true"
right click and paste in the formula you copied earlier
click the Format button in the dialog
set the font colour/style you want to use to highlight Fibbonaci
click ok to get out of the dialogs
you have now conditionally formatted just cell A3 to test for fibonnaci
you now have to use Format Painter to copy the test rules you set in cell A3 over the rest of the cells in your range as below:-
make sure cell A3 is selected
Home tab>>>> click on Format painter
select range A4:F2000
that has applied the test to all cells except B3:F3
click on cell A3 again
click on format painter
select range B3:F3
Job done, Fibbonaci numbers will be highlighted.
http://www.mediafire.com/view/w8igczbzm5nap02/scrnshot.png
I hope this helps
Example:-
copy this formula to your clipboard first. =OR(A3=1,A3=2,A3=5,A3=8,A3=13,A3=21,A3=34,A3=55,A3=55,A3=89)
click on cell A3
go to Home tab >>> conditional formatting.
Highlight cell rules >>> more rules
choose use a formula to determine which cells to format
in the box "format values where this formula is true"
right click and paste in the formula you copied earlier
click the Format button in the dialog
set the font colour/style you want to use to highlight Fibbonaci
click ok to get out of the dialogs
you have now conditionally formatted just cell A3 to test for fibonnaci
you now have to use Format Painter to copy the test rules you set in cell A3 over the rest of the cells in your range as below:-
make sure cell A3 is selected
Home tab>>>> click on Format painter
select range A4:F2000
that has applied the test to all cells except B3:F3
click on cell A3 again
click on format painter
select range B3:F3
Job done, Fibbonaci numbers will be highlighted.
http://www.mediafire.com/view/w8igczbzm5nap02/scrnshot.png
I hope this helps
Frank
Member
Well for 649, 659 , 749, 759 type lotteries or similar I think it's wise always to enter your results in the first instance in drawn order. This is an exact representation of what happenned on the day. You then have a store of 'as drawn' results which might be hard to come by retrospectively from a site. Then you can sort them ascending for the majority of conventional analysis techniques, skip, frequency, digital endings, combinational type and so on where original order makes no difference.
There are a few things you might like to analyse taking drawn order into account, these events happen at much larger odds and therefore less frequently than they would where order did not matter. You will have noticed that the late Moses always worked with drawn order and often included the bonus ball to lengthen the odds of repeat quintuplets in position even more. He was particularly interested in this phenomenon.
A few years ago I wrote a post describing the probability of a ball appearing in a specific position after sorting and went on to develop a spreadsheet to work these probabilities out in a table according to ball value and its position after sorting. They are all surprisingly different.
However, no such complication with numbers in drawn order, obviously all balls have equal probability of appearing in any 'as drawn' position and you could for example monitor this in real results to check for anomalies and discover what's overdue to be drawn fourth for example.
i think for the majority of these types of lotteries sorted order is sufficient, and a smaller number of draws in the database is required in order to have a representative sample. With drawn order a much bigger database is required to spot trends due to the rarity of exact position quintuplets etc. Whilst you are waiting to build the database the lottery might change its matrix like the UK lotto did!
There are a few things you might like to analyse taking drawn order into account, these events happen at much larger odds and therefore less frequently than they would where order did not matter. You will have noticed that the late Moses always worked with drawn order and often included the bonus ball to lengthen the odds of repeat quintuplets in position even more. He was particularly interested in this phenomenon.
A few years ago I wrote a post describing the probability of a ball appearing in a specific position after sorting and went on to develop a spreadsheet to work these probabilities out in a table according to ball value and its position after sorting. They are all surprisingly different.
However, no such complication with numbers in drawn order, obviously all balls have equal probability of appearing in any 'as drawn' position and you could for example monitor this in real results to check for anomalies and discover what's overdue to be drawn fourth for example.
i think for the majority of these types of lotteries sorted order is sufficient, and a smaller number of draws in the database is required in order to have a representative sample. With drawn order a much bigger database is required to spot trends due to the rarity of exact position quintuplets etc. Whilst you are waiting to build the database the lottery might change its matrix like the UK lotto did!
time*treat
Member
Fibonacci Marker macro
Haven't done one of these in a while and you'll have to re-indent in the right places ...
Option Explicit
Option Base 1
Sub Fibonacci__Marker()
'by time*treat, Dec 31, 2016'
'Looks through a rectangular grid of numbers and'
'colors the cells containing Fibonacci (or other preferred) values'
'This beta version works on the active sheet and is not yet completely idiot-proof. :^)'
'It doesn't handle merged cells, but checks for non-numeric text.'
'Written/tested in Excel 2003'
Dim Fibonacci__List() As Variant, positions As Long, Fib__Flag As Boolean
Dim upper__row As Long, left__col As Long
Dim lower__row As Long, right__col As Long
Dim working__row As Long, working__col As Long
Dim marking__color As Long, caution__color As Long
Const light__green As Long = 35
Const light__orange As Long = 40
marking__color = light__green
caution__color = light__orange
Fibonacci__List = Array(1, 2, 5, 8, 13, 21, 34, 55, 89)
upper__row = 3: left__col = 1
lower__row = 2000: right__col = 6
For working__row = upper__row To lower__row
For working__col = left__col To right__col
Fib__Flag = False
If IsNumeric(Cells(working__row, working__col).Value) Then
For positions = 1 To 9 'number of items in Fibonacci__List array'
If Cells(working__row, working__col).Value = Fibonacci__List(positions) Then
Cells(working__row, working__col).Interior.ColorIndex = marking__color
Fib__Flag = True
Exit For 'positions'
End If
Next positions
If Fib__Flag = False Then Cells(working__row, working__col).Interior.ColorIndex = xlNone
Else
Cells(working__row, working__col).Interior.ColorIndex = caution__color
End If 'IsNumeric'
Next working__col
Next working__row
Cells.Columns.AutoFit 'optional'
End Sub
Haven't done one of these in a while and you'll have to re-indent in the right places ...
Option Explicit
Option Base 1
Sub Fibonacci__Marker()
'by time*treat, Dec 31, 2016'
'Looks through a rectangular grid of numbers and'
'colors the cells containing Fibonacci (or other preferred) values'
'This beta version works on the active sheet and is not yet completely idiot-proof. :^)'
'It doesn't handle merged cells, but checks for non-numeric text.'
'Written/tested in Excel 2003'
Dim Fibonacci__List() As Variant, positions As Long, Fib__Flag As Boolean
Dim upper__row As Long, left__col As Long
Dim lower__row As Long, right__col As Long
Dim working__row As Long, working__col As Long
Dim marking__color As Long, caution__color As Long
Const light__green As Long = 35
Const light__orange As Long = 40
marking__color = light__green
caution__color = light__orange
Fibonacci__List = Array(1, 2, 5, 8, 13, 21, 34, 55, 89)
upper__row = 3: left__col = 1
lower__row = 2000: right__col = 6
For working__row = upper__row To lower__row
For working__col = left__col To right__col
Fib__Flag = False
If IsNumeric(Cells(working__row, working__col).Value) Then
For positions = 1 To 9 'number of items in Fibonacci__List array'
If Cells(working__row, working__col).Value = Fibonacci__List(positions) Then
Cells(working__row, working__col).Interior.ColorIndex = marking__color
Fib__Flag = True
Exit For 'positions'
End If
Next positions
If Fib__Flag = False Then Cells(working__row, working__col).Interior.ColorIndex = xlNone
Else
Cells(working__row, working__col).Interior.ColorIndex = caution__color
End If 'IsNumeric'
Next working__col
Next working__row
Cells.Columns.AutoFit 'optional'
End Sub