Pi day of the Century

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.

This entry was posted in Charts, Excel, Maths, Newton and tagged , , , , , . Bookmark the permalink.

6 Responses to Pi day of the Century

  1. Hui... says:

    I actually wrote the post in the Excelhero.ocm/blog as well



    • dougaj4 says:

      Hi Hui – I’ll go back and have a proper read!

      Are you doing anything for pi day this year?


    • dougaj4 says:

      Hui – I found the Hot Dog Pi post you wrote at Excel Hero, and I have added a link to that. The first link was to Monte Carlo Pi, which says it was by Daniel, but please let me know if I have that wrong.
      Browsing through the Excel Hero site I found a lot of stuff I’d missed before, I particularly liked your Escher based images. I must go and have a proper look when I have time. I just looked up Escher and see it’s the 43rd anniversary of his death in a few days, so maybe I’ll have a Newton Escher Bach day.

      Talking of illusions, one of the things I liked about my random pi images was that in the “crossing” plot many of the lines look curved (to me), when in fact they are all straight. Do you get that as well?


      • Hui... says:


        As you know ExcelHero is Daniel Ferry’s Blog
        I however do post there but I post as Daniel, not as myself
        He hasn’t worked out how to setup a separate login for me to post?

        I am responsible for the following posts at Excel Hero:

        Calculate Pi by Throwing Hotdogs !
        Motion Induced Blindness
        Excel Dancing Pendulums
        Lilac Chaser
        Counter-Intuitive Illusory Contours
        M. C. Escher
        Two Sinusoids
        Cafe Wall
        Curry’s Paradox



      • Hui... says:

        I see curved lines as well, mostly to the sides
        I suspect the curved lines are just an optical illusion caused by the adjacent lines and our minds trying to make sense of it all


  2. Leon says:

    We all ❤ pi day haha 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.