Image 1Estimating Area of the Circle Using Excel.
In this investigation we will try to find an approximation for the area of the circle using Spreadsheet.
For this purpose we need to develop a strategy to find the area of the circle.
Before that I would like to pose a question.
Question: Assume you have a circle and square tiles. Each side of the tile is 1 inch and the radius of the circle is an integer.
For example you have a circle with radius 3.
Image 1
Assume you started from origin to place tiles one by one
Image 2
a. What is the number of the tiles that you can put inside of the quarter circle?
b. What is the number of the tiles that exactly covers quarter circle?
For example, for radius 3 :
Image 3
Answer for (a) is 4 and Answer for (b) is 9
For r=4 ?
Image 4
Image 5
Answer for (a) is 8 and Answer for (b) is 15.
For r=5 ?
Image 6
Image 7
We need to find a numbering system to represent squares in this picture.
I think every square has a unique south-west coordinate, and we can use this information to label squares (tiles).
(1,2) represents
(Image 8) and
(4,2) represents
.(Image 9)
If you look carefully, If the square is completely inside the circle then south-west and north-east coordinates of the square is in or on the circle.
If circle crosses over a square then south-west corner is inside the circle but north-east corner of the square is outside of the circle.
We can also say that if (a,b) represents a square then (a+1,b+1) is the north-east coordinate of this square
Then we can conclude that we have two different square type:
Type1: Both (a,b) and (a+1,b+1) corners of the square is in or on the circle , where (a,b) represents square (completely yellow squares)
Type2: (a,b) is in the circle and (a+1,b+1) is out side of the circle , where (a,b) represents square (if at least one side of the square is red)
How to identify these squares in Excel?
At this point very basic information will help us. First of all, circle is the set of points at a fixed radius, called the radius, from a given point (center). We use origin as center.
then a point (x,y) is on the circle if its distance from (0,0) is equal to
radius. Therefore 
. If
then this point is inside the circle. If
then this point is outside of the circle.
So;
| Types | South-west corner | North-east cornet | Type 1 |
 |
 |
| Type 2 |
 |
 |
| outside of the circle |
 |
 |
Table 1
Now we need to create our points on the Excel file. r will be our radius value.
We only need points (x,y) where 
and
How to create a data set like this in the excel.
Entering manually ? For r=10, you need to enter 100 numbers. For r=100, you need 10000 entries.
You may chose whatever you want but I have a better idea than manual entry.
For this example I will use 10 as radius.
Step 1: Enter "10" to cell B1. We can create number from 0 to 99 easily. (there are 100 numbers between 0 and 99)
To do that enter "0" to cell D1 and "=D1+1" to D2. then copy D2, until you fill cell D100.
Step 2 : Enter "=mod(D1,B$1)" to cell E1 and copy it down until E100.
You will see that, we just created numbers from 0 to 9, 10 times.
Step 3: Enter "=(D1-E1)/B$1" to cell F1 and copy it down until E100.
After this step we created out data set to count the square in Type1 and Type2.
Step 4: Assume E column represents a, and F column represents b coordinate of an square.
Then we need to know a+1 and b+1 to check if a square is in outside of the circle.
Enter "=E1+1" to cell H1 and copy it down until H100, and Enter "=F1+1" to cell I1 and copy it down until I100. Then H column represents a+1 and I represents b+1.
Now we can find 
and
to check a square being Type 1, Type 2, or
Type 3 (completely outside of the circle. In fact, when we find Type1 and Type2
squares, remaining squares will be automatically Type3.
Step 5: Enter "E1^2+F1^2" to cell K1 and copy it down until K100 (for )
. Enter "H1^2+I1^2" to cell L1 and copy it down until L100 (for).
Up to this point we constructed our data. We only need a test to decide if a square is Type1 or Type2.
Step 6: We can use Table 1 to determine if a square is Type1 or Type2
| Types | South-west corner | North-east cornet | Type 1 |
|
|
| Type 2 |
|
|
| outside of the circle |
|
|
Table 2
If
then K1<(r^2) , If
then L1<= (r^2).
If a square is Type1 then both
and
holds for that square
Test for Type 1: Then we can test it in Excel by writing "IF((K1<B$1^2)*AND(L1<=B$1^2),1,0)" to cell N1.
This makes cell N1=1 if square is Type 1 or N1=0 if square is not type 1. And then we can copy it down until cell N100.
Test for Type2: "IF((K1<B$1^2)*AND(L1>B$1^2),1,0)" to cell N1.
This makes cell O1=1 if square is Type 2 or O1=0 if square is not type 2. And then we can copy it down until cell O100.
AreaCircle5.xls is a copy up to this point.
What we did up to this point?
Column N says whether a square is type1 or not. That means if a cell in column N is 1 then that square is in the circle.
Column O says whether a square is type2 or not. That means if a cell in column O is 1 then that square is on the circle border.
Step 7: Now the problem is how we can count the square that are type1 and type 2.
Actually adding the cells in column N will give us the total number of squares that are Type1, similarly adding the cells in column O will give us the total number of squares that are Type2.
To do that enter "=SUM(N1:N100)" to cell N101 and "=SUM(O1:O100)" to cell O101
Then N101 will give us the number of squares in Type1 and O101 will give us the number of squares in Type2. (AreaCircle6.xls)
Now we can turn to the main problem.
a. What is the number of the tiles that you can put inside of the quarter circle?
b. What is the number of the tiles that exactly covers quarter circle?
The number in cell N101 , total number of squares in type1, gives the number of squares that you can put into a "quarter" of a circle. Therefore multiplying N101 by 4 gives us the number of square tiles that we can put into the circle.
How about the answer for b? To cover circle we need both type1 squares and type2 squares. Therefore sum of N101 and O101 would give us number of tiles to cover quarter circle. Then 4.(N101+O101) is the number of squares to cover whole circle.
What to with these values?
In fact every tile in our example has an area of 1 inch square, then number of square tiles is equal to area.
Therefore 4.N101 is is the total area of the squares that we put into the circle and 4.(N101+O101) is the total area of tiles that we used to cover circle.
The fact is 4.N101 is less then area of the circle and 4.(N101+O101) is greater than the area of the circle. Then we have a lower bound and upper bound for area of the circle.
Lets repeat this process for several radius values and make table
Table 3
It is clear from the table that there is no linear relationship between radius and lower bound (a) or upper bound (b).
Since the radius of the circle is the only measurement that we know about circle, there may be another relationship other than linear.
That why we will examine (a) , (b) and 
.
| r (radius) | |
 |
 |
| 3 | 9 | 16/9= 2.11 | 36/9= 4 |
| 4 | 16 | 32/16= 2.00 | 60/16= 3.75 |
| 5 | 25 | 60/25= 2.40 | 88/25= 3.32 |
| 6 | 36 | 88/36= 2.44 | 132/36= 3.66 |
| 10 | 100 | 276/100= 2.76 | 344/100= 3.44 |
| 15 | 225 | 648/225= 2.88 | 756/225= 3.36 |
| 20 | 400 | 1176/400= 2.94 | 1324/400= 3.31 |
| 30 | 900 | 2700/900= 3.00 | 2928/900= 3.25 |
| 40 | 1600 | 4864/1600= 3.04 | 5172/1600= 3.23 |
| 50 | 2500 | 7644/2500= 3.0576 | 8024/2500= 3.2096 |
| 80 | 6400 | 19760/6400= 3.0875 | 20388/6400= 3.1856 |
| 100 | 10000 | 31016/10000= 3.1016 | 31796/10000= 3.1796 |
| 200 | 40000 | 124828/40000= 3.1207 | 126408/40000= 3.1602 |
Table 4
Assume that we have (Area of the circle / )=K
, In each of Table 4, we have an upper bound and a lower bound estimation for K.
And it is obvious that we have a better estimation each time when we increase radius value.
Table shows evidence that the number we are looking for is between 3.12 and 3.16 . Actually this number called PI and there are better ways to estimate this number. This page uses integers to make an estimate.
If you like to learn a better and more advanced way than this way Proceed to AreaOfCircleAdvanced.htm Page
Keywords:
Estimating Pi value with Excel using integers.
Estimating area of circle using small square tiles.
