+ Reply to Thread
Results 1 to 9 of 9

Thread: Trying To Produce Random Distribution In Excel?

  1. #1
    Points: 59, Level: 1
    Level completed: 18%, Points required for next Level: 41

    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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
    Attached Images  

  2. #2
    Points: 59, Level: 1
    Level completed: 18%, Points required for next Level: 41

    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Trying To Produce Random Distribution In Excel?

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

  3. #3
    Points: 871, Level: 15
    Level completed: 72%, Points required for next Level: 29

    Location
    New Zealand
    Posts
    115
    Thanks
    2
    Thanked 23 Times in 22 Posts

    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)

  5. #4
    Points: 59, Level: 1
    Level completed: 18%, Points required for next Level: 41

    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Trying To Produce Random Distribution In Excel?

    Thanks for the reply Katxt

  6. #5
    TS Contributor
    Points: 12,627, Level: 73
    Level completed: 45%, Points required for next Level: 223
    Miner's Avatar
    Location
    Greater Milwaukee area
    Posts
    1,079
    Thanks
    28
    Thanked 363 Times in 325 Posts

    Re: Trying To Produce Random Distribution In Excel?

    Here are the first 6 simulations as recommended by katxt.
    Attached Images  

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

    Tomhgriff1 (01-17-2017)

  8. #6
    Points: 59, Level: 1
    Level completed: 18%, Points required for next Level: 41

    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    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. #7
    Beep
    Points: 81,812, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Awards:
    Posting AwardCommunity AwardDiscussion EnderFrequent Poster
    Dason's Avatar
    Location
    Ames, IA
    Posts
    12,719
    Thanks
    300
    Thanked 2,574 Times in 2,196 Posts

    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?
    I don't have emotions and sometimes that makes me very sad.

  10. #8
    Beep
    Points: 81,812, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Awards:
    Posting AwardCommunity AwardDiscussion EnderFrequent Poster
    Dason's Avatar
    Location
    Ames, IA
    Posts
    12,719
    Thanks
    300
    Thanked 2,574 Times in 2,196 Posts

    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.
    I don't have emotions and sometimes that makes me very sad.

  11. #9
    Points: 59, Level: 1
    Level completed: 18%, Points required for next Level: 41

    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Trying To Produce Random Distribution In Excel?


    Quote Originally Posted by Dason View Post
    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

+ Reply to Thread

           




Posting Permissions

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






Advertise on Talk Stats