Monte Carlo - Buffon Needle

The "Buffon Needle problem" seeks to determine the probability of a needle landing on one of a set of parallel lines at a set distance from each other.  Deep detail can be found at:  http://mathworld.wolfram.com/BuffonsNeedleProblem.html .  For this problem, I wanted to setup a Monte Carlo simulation to calculate the value of PI.  Interestingly, I quickly became much more involved in this problem that I had planned...

Base on the MathWorld example above, I had the equations to work the geometry, however, attempting to simply proved difficult.  The geometry in this problem is obviously quite involved.  I spent some time reviewing the SINE and COSINE functions as well as the basic laws of triangles.  In addition, Excel enjoys working in Radians instead of degrees as I was to learn... eventually.

  This problem assumes the lines are spaced one inch apart and the needle is one inch long.

My excel spreadsheet is setup as the following:

Column A - Random number to generate an acute angle (<=90).  This will be used to determine whether or not the needle crosses a line.  The random number generated is multiplied by 90 to get the angle value.  Distance is also a critical part of this equation.

Column B - Distance from the Center of the Needle.  This allows for simple math no matter which way the needle is pointing.  The excel equations would get quite complex if this was distance from anywhere else other than the center.  This is a random number divided by 2 because it can be no further than 0.50 from a line (as they are only 1 inch total apart).

Column C - Hit Line.  This is where the magic happens.  This column determines whether the needle is "hitting" (or crossing) a line.  It compares the distance from the center of the needle to the line, to the length of the needle at the defined angle (using the sine function and dividing by 2 to account, again, for the middle of the needle measurement point).  If the sine of the needle angle (converted to radians for excel's pleasure) is greater than the distance to the line, the needle will cross the line.  This is counted as a hit and marked as a 1.  Misses are counted as a 0.

**Monte Carlo Action**

Now that the geometry is correct, the Monte Carlo functionality comes into play.  Initially, I ran this series of columns out to 65,000 rows.  In his equation, Buffon reasoned that the odds should come down to 2/PI or about a 63% chance.  Thus, I set out with the Monte Carlo simulation to demonstrate this and see how accurate PI could be calculated.  Through taking 2 over the hit rate, I was able to get "close" to PI.  

At 65,000 rows, taking a sample of 200 entries, the results were an average of 3.14127631 with a standard deviation of 0.00877293.  Of course, PI itself is 3.14159 and change.  Thus the Monte Carlo simulation proved that Buffon was correct in his predictions.  It also proved that PI could be calculated from a large data set.  Overall, this took several hours (a significant more than planned), but it was a very interesting project.  I learned quite a bit about Excel 2007, Monte Carlo simulations, and had a nice refresher on geometry.