In North America, where today is 3-14-15, it is Pi day, and since there are not 31 days in April, the rest of us have to go along with that as well.

Not only is it Pi day, it’s the best approximation to Pi we will get for the next 100 years, because the year ends in 15.

To mark the occasion I have set up a spreadsheet to calculate pi by drawing random lines, based on blog posts by Excel Hero, Hui at Chandoo.org, and also by Hui, another one from Excel Hero.

My spreadsheet is similar to Hui’s “hot-dog” throwing method, but since computers can throw lines much more accurately than people, I have adjusted it to produce a different looking chart.

In the spreadsheet I have set up a table to generate 1000 straight lines of length 1, with centre point exactly at X = 0, and Y between -0.5 and +0.5.  The lines have a random angle to the X axis so some will cross the X axis and some won’t.  The approximate value of Pi is given by: Pi = 2*N / X
where N is the total number of lines (1000 in this case) and X is the number of lines that cross the X axis.

The top of the table looks like:

Since 1000 lines does not give a very good approximation to Pi I have also written a UDF that will do the same calculation, but much more quickly because it doesn’t write all the line coordinates to the spreadsheet. With 10 million lines it gives a not too bad approximation:

If we plot an XY chart showing just the lines that cross the x-axis it forms a reasonable approximation to an ellipse:

Whereas the lines that do not cross form a square with a semi-circle at the top and bottom:

The spreadsheet may be downloaded from RandPi2.xlsb.

Have a great Pi Day.

Edit, Still Pi Day: I have just discovered that the term Pi was invented by a Welsh Man named William Jones.

Edit 21:40 15 Mar 2015; still Pi Day somewhere*:  I happened to click on an old comment and found a link to: Estimating pi using the MC technique

* The somewhere where it is still Pi day is:

The following countries use GMT-12 as their Time Zone.  NB No Summer Time / Daylight Saving Time is used.

Baker Island: an uninhabited atoll located just north of the equator in the central Pacific Ocean about 3,100 kilometres (1,900 miles) southwest of Honolulu.