Built a formula or macro from Markov rules ?

[serge]

I wanted to ask if it would be possible to create a formula or macro with Markov rules in this website having the data in excel from A1:A490 ?

Markov Chains | Brilliant Math & Science Wiki

A Markov chain is a mathematical system that experiences transitions from one state to another according to certain probabilistic rules. The defining characteristic of a Markov chain is that no matter how the process arrived at its present state, the possible future states are fixed. In other...

brilliant.org

Thank you.

 

[nemmi69]

Sub Markov_macro()
'
' Markov_macro Macro
' Macro that transforms data into what can be used for Markov Chain
'for gsn casino
' Keyboard Shortcut: Ctrl+Shift+M
'
'declarations
Dim start_row As Integer
Dim end_row As Integer
Dim i As Integer
Dim column_to_fill As Integer
Dim column_to_check As Integer

'initializations
end_row = 130
start_row = 365
column_to_fill = 12
column_to_check = 11
'Fills the rows
For i = start_row To end_row
        If Worksheets("Table").Cells(i, column_to_check).Value >= 0 Then
        Worksheets("Table").Cells(i, column_to_fill).Value = 1
        Else
        Worksheets("Table").Cells(i, column_to_fill).Value = 0
        End If
        
Next i
        

End Sub

 

[serge]

Hi nemmi69,
Thank you for taking the time to do this task, I really appreciate it.
But I'm not an Excel savvy guy, could you please explain very quick how to use this code, Thank you.

 

[nemmi69]

So
Dim start_row As Integer
Dim end_row As Integer
are the starting row and end row it will cover

Dim column_to_check As Integer
is the column it will go down

Dim column_to_fill As Integer
is which column to write to

change these as appropriate

 

[serge]

That is still Chinese for me, I will write my data in column A1 to hundred or thousands row, how do I incorporate the code in my excel file ?
Thank you.

 

[nemmi69]

You will need to insert a module and copy this to the module

1) Open the VBA editor. The fastest way to do this would be to press Alt + F11 on the keyboard.
2) Right-click on the Excel file VBA project name - this will be the name of the Excel workbook (or any other item below, for example the “Microsoft Excel Objects” folder icon as in the screenshot).
3) Go to “Insert” and click on “Module”.

Now, when the right side goes white a module will be open. Copy all the code here.

Make sure that your cursor is located within your pasted code. Then, click on the green Start button to run the macro. Alternatively, press F5 on the keyboard.

 

[Joe4]

Additionally, there are a lot of tutorials and videos out there that can be found with Google searches which will show you where & how to insert VBA code and how to run it.
Here is one, but you can find many more: How to Add Macro Code to Excel Workbook

 

[serge]

I follow your instructions but when I run the code nothing happen I must do something wrong ???
My data are in A1:A496 where should the return be ?

Up to 496 will the return of the code calculations will be in A497 ?

 

[nemmi69]

there will be 496 results

 

[serge]

Hi nemmi69,

Thank you for helping me, I thought that the formula in that website will calculate the string of data and return only 1 answer, so this is not what I was expected.
I'm looking for a program that will scan the history data and return the most probable answer.

I really appreciate your help, Thank you.