In Lab(1) you learned about Excel basics. You were able to modify the cells
using a formula and display your data as graphs. In this activity, we will
utilize some other utilities of Excel. As we illustrated in the demo in class,
Monte Carlo Simulations in various forms are used to determine the outcome the
solution to problems that may not have a solution or their solutions are not
easy to find.
Activity (1) - Producing a Circle of an Arbitrary Radius
How do we draw a circle using Scatter Plot option with data points that are generated on the edge of the circle?
By definition a point on a circle of radius defined by Radius is always Radius distance away from the center of the circle. Also, we know that in 2-D Cartesian Coordinate where a point is defined by its x and y coordinates, the relation:
Radius2 = x2 + y 2We want to draw a circle in Excel using the Scatter Plot option. Thus, we need to generate some data points on the circle that are equally spaced and connect these points to get the circle. To do this we will generate the points that are spaced based on their angles. Then we will use the angle and radius to get the x and y values.
y = Radius*sin(angle)
= Radius*cos(A4*pi( )/180)Note that we have multiplied the angle by Pi/180 to convert to Radian which is the default used in Excel. Since cos(0) is 1, if you press enter, you should get a value that is the same as the Radius. In Cell C4, compute the value for y by typing:
= Radius*sin(A4*pi( )/180)
Since sin(0) is 0, then the y value is 0 for this one.
Now highlight B4 and C4 and drag the small square at the bottom right-hand-side all the way to the end to copy the formula to all the cells up to 360. A small part of the spread sheet is shown below.
Now that we have the set of x and y data points, let's plot them in a Scatter Plot. Highlight all the x and y data points and click on Insert at the top, and plot them as a Scatter Plot. You will get a plot that looks like this.
After a little cleaning, by modifying the format for the x and y axes, we can get the appears more like a circle.
Then modify the min and max on both x and y axes.
And we can even use the mouse to make the drawing area a perfect square to get:
To re-cap, we were able to draw a circle by following these steps:
ACircle = PI * Radius2If we somehow we could find the area of circle, then we could use: PI = ACircle/Radius2
ACircle = (Ratio of circle to square)*area of squareWe will drop random points on the square and will count those that have fallen on the circle. If we do a good job in creating random points in the square then we get a get a ratio of the number that fell on the circle to that fell on the square.
points on the Square / Number of points on the Circle) = (Area of Square / Area
(NSquare / NCircle ) = (ASquare / ACircle )
and we can use this to find the area of circle:
= (NCircle / NSquare )*ASquare
PI = ACircle /Radius2, and Area of Square = 2Radius* 2 Radius = 4Radius2
PI = 4* (NCircle / NSquare )
Let's try to drop some random points on the 4-by-4 square we had in the plot above, and since we already have a circle there, count the number of points.
Our points must have x values between -4 to 4 and y values between -4 to 4. To generate a random point between -4 to 4, we utilize the random generator in Excel that produces a random number between 0 and 1 and then scale the number to the -4 to 4 range.
x = -Radius + rand( ) * (2*Random)You can clearly see that in this formula when rand() is 0, then the x will be -Radius, which is -4. And if the rand() is 1, then x = -Radius + 2Radius = Radius, which is 4. So we managed to scale the random numbers between 0 and 1 to that between -4 and 4.
5) In the new screen that looks like this:
We need to add a new Series, so click on Add Series, to get a frame like this:
Then on Series X values, click on
the small box with a red mark in the middle on the right-hand-side of the blank
field and then go to the spreadsheet and highlight from D4 to D23, and once
done click on the small box again to go back to the spreadsheet.
Repeat the same thing for the Series Y Values, and this time highlight E4 to E23.
Then click on Ok, once done.
This will produce a plot like this:
But we don't want the dots inside to be connected, so right click on one of the lines and click on Format Data Series, then click on Line Color, and then choose No Line, then click on Close. The new plot will look like this:
We have created 20 points, out of which 14 are inside. So the estimate for area is:
PI = (14/20)*4 = 1.42*4 = 2.8
Not a good estimate, but remember we have only used 20 data points.
With 2000 data points, I got 3.124.
I used 50 random points and came up with something like this:
How do we do this? Any guess?
Step 1: The paper is 8.5 on x and 11 on y, so we can generate random numbers for x between 0 to 8.5. Also we can generate random numbers between 0 to 11.
Step 2: We plotted the points in a Scatter Plot.
Then I used the Format Chart Area --> Fill Option --> Picture or texture fill.
Then I used File to find the image I had taken from the paper and the unknown shape and set the transparency level to 60%. Then clicked on Close.
Step 3: Click in the middle of the plot, Format Plot Area --> No Fill --> Close.
Now you should see a plots with the image as its background. You can count the points on the unknown area and estimate the area of the unknown shape.
We hope you enjoyed this lab.