Anyone?
Is this a typical chart of a 50/50 outcome over 1400 tests?
Hi,
Im trying to do some studies in excel to teach myself probabilities and the outcome of a study is pretty much the opposite of what i expected. I wanted to check if my hypothesis was wrong or my method.
I assumed a random coin toss over a large enough sample would revert back to zero over time as there shouldnt be a bias unless it was weighted but the results produced a large 1 way bias over a large sample (about 1400).
My method was to enter =randbetween(0,1) and get a list of 0's and 1's.
Then I made an IF Function to treat all 0's as -1 (obvs if i didnt do that the total number would just be positive)
I then totaled all these -1's and 1's and produced a line graph of that.
I expected it to mean revert around 0 a couple of times over that sample. But the chart produceda strong downtrend finishing at -90
I have attached an image
Can someone please explain why my hypothesis is wrong? Is the random generator on excel not random? I know in the short term trends of consecutive heads or tails exist but over the long run surely an even number of head streaks and tail streaks
Thanks a lot
Tom
Anyone?
Is this a typical chart of a 50/50 outcome over 1400 tests?
Strangely, this isn't a particularly unusual graph. The outcome of a random walk is far more variable than people often think.
Put 0 into A1. In At put your formula =A1+IF(RANDBETWEEN(0,1)=0,-1,1) then copy down 1000 rows. (This is probably what you did.) Draw your graph.
Now press the F9 key a few times and see the possibilities.
Tomhgriff1 (01-17-2017)
Thanks for the reply Katxt
Here are the first 6 simulations as recommended by katxt.
Tomhgriff1 (01-17-2017)
Thanks Miner, its fascinated. I thought there would be a lot more reversion to the mean.
If you were to create a bell curve i would assume a normal distribution where 60% of the coin flips would be producing a net result of 50/50 (or 0 in this example) but in reality there are more trends in the data and outliers than expected.
Your procedure seems fine but -90 seems a bit extreme. What happens if you replicate the experiment?
I don't have emotions and sometimes that makes me very sad.
Why would you expect 60% to give a result of 0? The longer the walk goes the smaller the probability that it ultimately ends at 0 would be.
I don't have emotions and sometimes that makes me very sad.
Tweet |