# Thread: Trying To Produce Random Distribution In Excel?

1. ## Trying To Produce Random Distribution In Excel?

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

2. ## Re: Trying To Produce Random Distribution In Excel?

Anyone?
Is this a typical chart of a 50/50 outcome over 1400 tests?

3. ## Re: Trying To Produce Random Distribution In Excel?

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.

4. ## The Following User Says Thank You to katxt For This Useful Post:

Tomhgriff1 (01-17-2017)

6. ## Re: Trying To Produce Random Distribution In Excel?

Here are the first 6 simulations as recommended by katxt.

7. ## The Following User Says Thank You to Miner For This Useful Post:

Tomhgriff1 (01-17-2017)

8. ## Re: Trying To Produce Random Distribution In Excel?

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.

9. ## Re: Trying To Produce Random Distribution In Excel?

Your procedure seems fine but -90 seems a bit extreme. What happens if you replicate the experiment?

10. ## Re: Trying To Produce Random Distribution In Excel?

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.

11. ## Re: Trying To Produce Random Distribution In Excel?

Originally Posted by Dason
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.
True, youre right. I assumed over a fixed set of data a normal bell curve will form whereby 60% of the data falls within 1 standard dev

 Tweet

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts