Simulation with Excel Series: Lab 2 Estimating Pi

Title: Estimating the value of pi (π) using Monte Carlo Simulation.

Basic Theory:

In this lab, we are trying to find the value of pi(π) using Monte Carlo Simulation. The Monte Carlo simulation deploys the concept of a large collection of random data that satisfy some property to perform simulations in diverse fields including computer science. In this case, we are applying it for Mathematical analysis of estimating the value of pi(π).

Procedure:

For estimating the value of pi(π), we first specify a square area and plots random points inside it. We have used 13,186 points in this simulation (a lot of points!). It is still very simple because we plot the points in the region bounded by -1 to 1 in both X and Y axes. This can be described using a random function in Excel as,

X = 1-2*RAND() and same for y

Now, we plot the points which fall within the region satisfied by the equation of a circle. So, for the circle the description of the Excel formula will be,

X = IF((X^2+Y^2)<1,X,0), Y = IF((X^2+Y^2)<1,Y,0)

This formula shows that the points will be plotted only if they satisfy x²+y²<1 i.e within the radius of the circle. Note that the circle is inside the square bounded by -1 to 1 in both axes, so radius of the circle is also 1. Now we can perform our calculation as,

Area of circle / Area of Square = Points that satisfy circle equation / Total points plotted

π r^2 / (r^2+ r^2+ r^2+ r^2) = Random value / 13186

So, π r^2/4r^2 = COUNTIF(X0:Xn,"<>0" ) / 13186

Here, we found this as π r^2/4 r^2 = 0.785312425                        (from our random data)

π = 4*0.785312425 = 3.141249701

Hence, we found the value of π.

Sample Data:

Table1: Sample Data for Monte Carlo Simulation for finding pi (π).

Rectangle		Circle
X	Y	X	Y
0.700356	-0.32303	0.700356	-0.32303
-0.165	-0.58146	-0.165	-0.58146
0.973976	-0.76557	0	0
-0.92966	0.373582	0	0
0.22543	-0.51423	0.22543	-0.51423
0.853136	-0.11127	0.853136	-0.11127

Output:

Below are the graph plots for the points in the circle and square.

Conclusion:

Here, we could see that the estimated value of π is very close to real value. We can further increase the accuracy of the simulation by increasing the number of random points. However, the excel worksheet will become too slow if we generate a lot of random numbers because of large number of calculations.  

Simulation with Excel Series: Lab 1 Random Walk

Hello readers!

I'm starting a short series of basic simulations using MS Excel using this post. We will not enter any advanced topics in simulation, yet try to understand how some common problems can be simulated with a well-known tool.

Title: Random Walk (movement) of multiple particles (points) in 2D plain.

Initial Condition: a) No. of Particles = 100

                               b) Particles start to move from origin (0,0)

Basic Theory:

In this lab, we are trying to simulate the motion of particles in a 2D space for discrete time frames. As per our assumption, we have 100 particles, all of which start moving from (0,0) coordinates and move randomly. Hence, it has been called as “Random Walk”.

Procedure:

We perform our basic simulation in Microsoft Excel using the “Scatter Plot” feature. We achieve this by organizing the random data in a spreadsheet and applying formulae to the spreadsheet cells. In it, we employ a random function to generate a random (stochastic) sample of data for 100 particles in 11 discrete time frames (t0, t1… t11). For the initial state (t0), all the particles stay at (0,0) coordinates, and the next position of the particle is calculated using the formula:

X-position at last time + 1-2*RAND() and Y-position at last time + 1-2*RAND(), in which 1-2*RAND() gives us a value between -1 and 1.

Thus, the next position of the particle becomes unknown to us, and we can simulate random movement of the particles in 2D space. Finally, we can see the results by selecting the data for one particular time, clicking the “Insert” menu and selecting “Scatter”.   

The sample data for 4 particles is tabulated in next section (as showing 100 becomes very long).

Sample Data:

Table1: Sample Data for 4 particles for 4 discrete time frames.

	Time = 0		Time = 1		Time = 2		Time = 3
	X	Y	X	Y	X	Y	X	Y
Particle1	0	0	0.230713	0.414474	0.197587	0.397396	0.705377	-0.43751
Particle2	0	0	-0.97556	-0.74393	-1.06599	-0.3071	-0.95934	-0.72553
Particle3	0	0	0.530602	0.295072	0.233677	-0.53365	-0.74835	-0.83489
Particle4	0	0	-0.57715	0.912138	0.028148	1.523969	-0.1651	1.801373

Output:

Below are the scatter plots for four different time frames. We can clearly see the randomness in the plotted data.