Most of us know that theoretically, there’s a 50-50 chance of getting a “head” from an unbiased coin-toss. There are numerous implementations and simulations done in virtually all programming languages around this age-old ‘riddle’. In this blog, I share a simple but very effective simulation in Excel.
Here’s the simulation:
As you can see, I’m comparing 4 different sets of flips. Each collection (i.e. trial) is made of different number of tosses ranging from 100 to 1,000 to 500,000 to 1,000,000. The purpose is to actually demonstrate that as the number of tosses increase, the statistical probability of 50-50 chance becomes more and more apparent. At lower number of trials, the outcomes are less predictable. It’s an important concept to always remember: The smaller your sample (in anything), the more error-prone or random your results are going to be; and the opposite is true in most statistical situations.
So, how is it done in Excel?
I generated a million outcomes using RANDARRAY function denoting 1 for Head and 2 for Tail.
all the way to 1M rows: 
Once I have that, I can extract, using formula how many Heads and Tails I got in that trial. I save the result in a table. Do the same for the other trials with different sample sizes. Save them in respective tables. Then create chart for each table. Now it’s all set. All I have to do is re-calculate in a loop (use a macro or VBA for continuous loop or to specific limits) and watch the mesmerizing updates of charts.
Quick! What number would you pick between 1 and 10 (inclusive)? Read my next blog about it.
