Simulation with Excel Series: Lab 3 Estimating Integrals

Title: Estimating the value of integrations of different functions using Monte Carlo Simulation.

Basic Theory:

In this lab, we are trying to find the value of integration of a linear and a quadratic function 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 area under the curve i.e. integration.

Procedure:

For estimating the value of integration, we first specify a square area and plots random points inside it. We have used 1000 points in this simulation. It is still very simple because we plot the points in specific regions with well defined ranges for X and Y coordinates. We then use absolute referencing to generate random values within the range. This can be described using a random function in Excel as,

X = $X$0+($X$1-$X$0)*RAND()

This is similar for y as well.

Now, we plot the points which fall within the region satisfied by the equation of the function. So, for the equation y = x the description of the Excel formula will be,

Y = =IF(Yn<Xn,Yn,0)

This formula shows that the points will be plotted only if they satisfy y = x. Now we can perform our calculation as,

Integration = n/N*c(b-a)

Where, c is height, b and a are x-ranges. Also, c(b-a) is the area of rectangle here.

We now count the points under the curve using,

n = COUNTIF(Y0:Y1000,"<>0" )

So, integration will be n/1000 * Area of Rectangle.

Hence, we found the value of value of integration for y = x. We can use the same process to find the value of integration for the curve y = x² except that we have to use Y = IF(Yn<Xn^2,Yn,0).

Sample Data:

Table1: Sample Data for Monte Carlo Simulation for finding integration value for y = x.

X	Y	Curve X	Curve Y
3.172925628	4.756267552	3.172925628	0
4.025446279	1.020977669	4.025446279	1.020977669
4.859458217	4.077230586	4.859458217	4.077230586
0.660807938	5.691479042	0.660807938	0
2.835472595	0.202967389	2.835472595	0.202967389
4.46133673	5.299769082	4.46133673	0
2.104981223	0.039854532	2.104981223	0.039854532
2.920367193	6.808772442	2.920367193	0
2.426309885	0.417571468	2.426309885	0.417571468

Output:

Below are the graph plots for the points below the curve y = x and y= x².

Results:

The results of our simulation were,

Curve	Simulated Value	Actual Value
y = x	12.39	12.5
y = x2	5250	5208.33