Analytics Coding Education STEM

Simulating coin-toss in Excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top