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.

### Like this:

Like Loading...

*Related*

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

Hui…

LikeLike

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

Are you doing anything for pi day this year?

LikeLike

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?

LikeLike

Doug

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…

LikeLike

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

LikeLike

We all ❤ pi day haha 🙂

LikeLike