Any probability experts or users of markov chains

vampsthevampyre

New Member
Joined
Apr 29, 2016
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Afternoon All,

Just been watching a video regarding probability of coin flips. The initial set up I can understand, how to create a state chart (see attached spreadsheet) of the chances of 7 heads in a row. But after this the video stated that to simulate the flipping a coin 200 times. the video states that if you raise the initial state matrix to the power of 200 you will then get the second matrix which shows the probability of getting 7 heads in a row raises to 0.54 or 54%.

I've tried searching for how to recreate this in excel but the maths goes way beyond my knowledge and the solutions also show markov chains and that really makes my head hurt. Is this possible to do in excel? and if so how.

Also how easy is it to change the number of flips and introduce a biased coin (i.e. 67% tails, 23% heads)

Any help would be appreciated

Regards

Ian


roulette.xlsx
BCDEFGHIJKLMNOPQRSTUV
3After 200 flips
4Next StateNext State
50123456701234567
600.50.500000000.230.110.060.030.010.010.0040.54
710.500.50000010.230.110.060.030.010.010.0040.55
820.5000.5000020.220.110.060.030.010.010.0040.55
9Current30.50000.5000Current30.220.110.050.030.010.010.0030.57
10State40.500000.500State40.20.10.050.030.010.010.0030.6
1150.5000000.5050.170.090.040.020.010.010.0030.66
1260.50000000.560.110.060.030.020.010.0030.0020.77
1370.50000001700000001
Sheet10
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
It might be helpful to give us a link to the video so we understand what context you are in.

the video states that if you raise the initial state matrix to the power of 200 you will then get the second matrix which shows the probability of getting 7 heads in a row raises to 0.54 or 54%.
I had to read this a couple of times before I realized what you meant. If you flip a coin 7 times, the chances of all heads is 1/2^7 = 1/128 = 0.0078125. But you are talking about if you flip a coin 200 times, what is the probability that somewhere in those 200 flips is a run of 7 heads?

A coin flip is a Markov "chain" because the probability of the next event is not dependent on any past events. That is, the chances of getting heads on a coin flip is 50%; if you flip 6 straight heads, the chances of heads on the next flip is still 50%. (See also Gambler's Fallacy.)

I don't understand how you are trying to solve any of this in the Excel sheet you showed in your post. Determining the chances of 7 heads in 7 flips is analytical (as I showed above) and you do not need to enumerate the "states". You can to that understand how the probability works, but the problem is pretty simple.

To determine the chances of a run of 7 heads in 200 flips is more complicated. But I still don't see what you are trying to do on the Excel sheet. To model 200 coin flips exhaustively you would have to generate 2^200 = 1.61 x 10^60 possible sequences. I don't know if there is an analytical way to do this, that is, an expression you can solve. The way I would do this is a Monte Carlo simulation in VBA.

Once you have the method, it's not a big deal to change the fairness of the coin. It's still a binary variable.
 
Upvote 0
You need to raise the first matrix to the power of the number of flips. The answer will occur in the last column of the first row of the resulting matrix.
By the way the last entry in the first column of your starting matrix should be 0, not .5)

There's not a native way to raise a matrix to power that I know of, other than using MMULT n - 1 times. So that is not reasonable to do for large n by using a formula. A recursive lambda could be used but VBA is more straight-forward and isn't affected by the stack limit as the lambda would be for large number of flips.

Here is VBA function MatrixPower which can raise a square matrix to a power.

VBA Code:
Function MatrixPower(m, n As Long)
    Dim i As Long, s, c
    s = m
    c = m
    For i = 2 To n
        s = Application.MMult(c, s)
    Next
    MatrixPower = s
End Function

So raising the first matrix to the 200th power yeilds the second one. The answer appears in the last cell of the first row.

Book2
ABCDEFGHIJKLMNOPQR
10.50.50000000.2290660.1149950.057730.0289810.0145490.0073040.0036670.543708
20.500.5000000.2272260.1140710.0572660.0287480.0144320.0072450.0036370.547375
30.5000.500000.2235590.112230.0563420.0282840.0141990.0071280.0035790.554678
40.50000.50000.2162550.1085640.0545010.027360.0137350.0068950.0034620.569228
50.500000.5000.2017060.101260.0508340.025520.0128110.0064310.0032290.598209
60.5000000.500.1727250.0867110.043530.0218530.0109710.0055070.0027650.655938
70.50000000.50.1149950.057730.0289810.0145490.0073040.0036670.0018410.770934
80000000100000001
9
10
11Answer:0.543708
12
Sheet2
Cell Formulas
RangeFormula
J1:Q8J1=MatrixPower(A1:H8,200)
B11B11=INDEX(J1#,1,8)
Dynamic array formulas.


To change the probability you would change all the .5 's in the first matrix to the new probability of heads.
To change the number of flips just change the power used in the MatrixPower function.
To change the number of heads in a row you are interested in, you would adjust the size of the first matrix maintaining the same pattern. If the run is 7 the matrix wil be 8x8, or in general if the run of interest is n the matrix will be (n+1)X(n+1).
 
Upvote 0
Correction: To change the probability you would change all the .5 's in the first matrix (Column 2 and beyond) to the new probability of heads. Change all the .5 's in column 1 to (1 - the probability of heads).

Here's a Lambda that does all that using a call to the vba MatrixPower function.

Excel Formula:
LAMBDA(flips,probability,runlength, LET(m,TEXT(SEQUENCE(runlength, runlength,0,0) +SEQUENCE(runlength,,100,100) + SEQUENCE(1,runlength),"0000"), mm, IF(LEFT(m,2)=RIGHT(m,2),probability,0), mmm, HSTACK(SEQUENCE(runlength,,1-probability,0), mm), mmmm, VSTACK(mmm, HSTACK(SEQUENCE(1,runlength,0,0),1)), INDEX(MatrixPower(mmmm, flips),1,runlength+1)))
 
Upvote 0
Correction: To change the probability you would change all the .5 's in the first matrix (Column 2 and beyond) to the new probability of heads. Change all the .5 's in column 1 to (1 - the probability of heads).

Here's a Lambda that does all that using a call to the vba MatrixPower function.

Excel Formula:
LAMBDA(flips,probability,runlength, LET(m,TEXT(SEQUENCE(runlength, runlength,0,0) +SEQUENCE(runlength,,100,100) + SEQUENCE(1,runlength),"0000"), mm, IF(LEFT(m,2)=RIGHT(m,2),probability,0), mmm, HSTACK(SEQUENCE(runlength,,1-probability,0), mm), mmmm, VSTACK(mmm, HSTACK(SEQUENCE(1,runlength,0,0),1)), INDEX(MatrixPower(mmmm, flips),1,runlength+1)))
@JGordon11 Honest question: why did you use LAMBDA here instead of VBA? What is the advantage that I am missing?
 
Upvote 0
There are online calculators for this kind of thing ....
Interesting link, thanks. I hadn't encountered the k-step Fibonacci method before.

You can get the result directly as 1 - (200th Heptanacci / 2^200)

= 1 - 7.332333E+59 / 1.606938E+60 = 0.543708 (as in the matrix approach above)
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top