The Area of an Irregular Polygon
When the preceding method is used to estimate the area of an irregular figure, we need a general method to determine whether a given point is inside or outside the figure. In the following, the figure must be a polygon, that is, a figure that can be described by a series of coordinates connected by straight lines. Since in an Excel chart, a curve can be approximated by a number of straight line segments, in theory a figure of any shape can be handled.
The standard method to determine whether a point lies inside or outside the figure is to draw a "ray" from the point extending out to infinity. In this example, illustrated in Figure 1515, a "ray" is drawn vertically upwards from the point. If the ray crosses the boundary line(s) of the figure an odd number of times, the point lies inside the figure.
Figure 1515. Determining whether a point lies inside or outside an irregular polygon.
X coordinate of point
Figure 1515. Determining whether a point lies inside or outside an irregular polygon.
The procedure to test whether a point xa, yA lies within the figure is as follows:
For each of the N edges that make up the figure:
(1) If the x coordinates of both ends of the edge lie to the left of xA, then go to the next edge.
(2) If the x coordinates of both ends of the edge are to the right of xA, then go to the next edge.
(3) If the y coordinates of both ends of the edge are below yA, then go to the next edge.
(4) If none of the above is true, the y coordinates of one or both ends of the edge are above the point. Determine the y coordinate of the "crossing point" where the vertical ray and the edge cross, using the formula xR xL
(5) If yc > yA, the ray crosses the edge of the polygon, so add one to the number of crossings found, and go to the next edge.
(6) When all Wedges have been evaluated, if the number of crossings is odd, the point lies inside the figure.
This "inside or outside" calculation can be done either with worksheet formulas or with a VBA custom function. The following portion of a spreadsheet (Figure 1516) illustrates the calculation using worksheet formulas.
A 
B 
C 
0 
E 
F  
4 
Does segment have...  
Both x 
Both x 
Both y 
YcYa  
5 
X 
y 
to left? 
to right? 
below? 
positive? 
6 
27 
31  
7 
9 
28 
TRUE  
8 
5 
11 
TRUE  
9 
31 
2 
TRUE  
10 
44 
17 
FALSE 
FALSE 
FALSE 
FALSE 
11 
28 
20 
FALSE 
FALSE 
FALSE 
TRUE 
12 
36 
22 
FALSE 
FALSE 
FALSE 
TRUE 
13 
35 
31 
FALSE 
TRUE  
14 
27 
31 
FALSE 
FALSE 
FALSE 
TRUE 
15 
(Coords In row above echo the first set of coords)  
16  
17 
Enter coords here 
Inside?  
18 
Yo 
(by formula)  
19 
32 
15 
TRUE  
20 
Figure 1516. Inside/outside determined by using worksheet formulas, (folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', sheet 'Single Point Diagram')
Figure 1516. Inside/outside determined by using worksheet formulas, (folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', sheet 'Single Point Diagram')
Note that, in the table of coordinates of the line segments that describe the figure (A6:B14 in Figure 1516), the coordinates of the initial point are repeated in line 15 so as to complete the figure. (This of course is also necessary to create a chart of the figure.) Thus the nine rows of points shown in Figure 1516 describe eight line segments. That's why there are formulas in rows 7 through 14, but not in row 6. (It would be equally suitable to have formulas in rows 6 through 13 and not in row 14.) The formulas in row 7 are: in cell C7: =AND($A$19>A6,$A$19>A7) in cell D7: =IF(C7=TRUE,"",AND($A$19<A6,$A$19<A7)) in cell E7: =IF(OR(D7=TRUE,D7=""),"",AND($B$19>B6,$B$19>B7))
in cell F7: =IF(E7=FALSE,(B6+(B7B6)*($A$19A6)/(A7A6)$B$19)>0,"") and the formula in cell C19 (an array formula) is {=MOD(SUM((F6:F14=TRUE)*1),2)oO}
The following VBA code illustrates how to perform the "inside or outside" calculation by means of a custom function. The function takes four arguments: the range of x values describing the figure, the corresponding range of y values, the x coordinate of the point to be tested and the y coordinate of the point. The function returns TRUE if the point is inside the figure, otherwise FALSE.
Function lnside(x_values, y_values, x_point, y_point) As Boolean
Dim N As Integer, J As Integer, C As Integer Dim YC As Double
N = x_values.Count 'Does figure have closure?
If x_values(1) <> x_values(N) Or y_values(1) <> y_values(N) Then Inside = CVErr(xlErrValue): Exit Function For J = 1 To N 1
If x_values(J).Formula ="" Or y_values(J).Formula = "" Then Inside = _ CVErr(xlErrValue): Exit Function 'Exit if cell is blank 'Both ends of segment to left of point?
If x_point >= x_values(J) And x_point > x_values(J + 1) Then GoTo EOL 'Both ends of segment to right of point?
If x_point <= x_values(J) And x_point < x_values(J + 1) Then GoTo EOL 'Both ends of segment below point ?
If y_point >= y_values(J) And y_point > y_values(J + 1) Then GoTo EOL 'If came here, one or both ends of the segment are above the point. 'Calculate the y coordinate where the "ray" crosses the segment. YC = y_values(J + 1) + (y_values(J)  y_values(J + 1)) _ * (x_point  x_values(J + 1)) / (x_values(J)  x_values(J + 1)) 'if the crossing is above the point then add one to the count If YC  y_point > 0 Then C = C + 1 EOL: Next J Inside = C Mod 2 End Function
Figure 1517. VBA code to determine inside/outside, (folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', module 'Module 1')
Figure 1519 illustrates the use of the custom function to estimate the area of an irregular polygon such as the one shown in Figure 1518. The values in cells $A$6:$B$14 specify the vertices of the polygon. The formulas in cells A17 and B17 use the RAND function to specify the x and y coordinates of a point within the area bounded by x = 0 to x = 50 and y = 0 to y = 35; the formulas are, respectively,
and the formula in C17 contains the custom function =lnside($A$6:$A$14,$B$6:$B$14,A17,B17)
The formulas were filled down to fill 2000 cells. The formulas to calculate the area are:
in cell D7: =COUNTA($C$17:$C$2016) (total number of points) in cell E7: {=SUM(($C$17:$C$2016)*1)} (number of points inside polygon) in cell E9: =E7/D7 (fraction of points inside)
in cell E11: =E9*D11 (area of polygon)
To plot only the points that lie within the polygon, the formula =!F($C17,A17,"") in cell D17 and the formula =IF($C17,B17,"")
in cell E17 would seem to be suitable. These formulas, when filled down, yield the spreadsheet shown in Figure 1519. But nullstring values are plotted as zeros in a chart, so the chart doesn't turn out the way we want. Instead we use the NA() worksheet function; cells containing #NA! values are not plotted.
X coordinate
A 
B 
c 
0 
E I  
5 
X 
Y 
Calculation of area  
6 
27 
18 
# points 
ft insicte  
7 
9 
26 
2000 
823  
8 
5 
11 
fraction  
9 
31 
6 
0.3115  
10 
44 
27 
tota! area 
area  
11 
28 
20 
1750 
545.1  
12 
36 
30.5  
13 
22 
30,5  
14 
27 
18 (This row echoes the first set of coords)  
15 
(2000 random points between x = 0 and x = 50, y = 0 and y = 35)  
16 
Xa 
Ya 
Inside? 
X and V for 
plotting point 
17 
31 54968765 
19.50260778 
TRUE 
31,54968765 
19.50260778 
18 
43.10121712 
18.55383518 
FALSE  
19 
32.67182151 
19.80326911 
TRUE 
32.87162151 
19.80326911 
20 
36.71470616 
15.93267943 
TRUE 
36 71470816 
15.93267943 
21 
36 90764745 
18.47652055 
TRUE 
38,90754745 
16.47652055 
22 
44 32583965 
27,69863231 
FALSE  
23 
7.71297725 
24.09107738 
FALSE  
24 
27.98293313 
17.55974824 
TRUE 
27.96293313 
17.55974624 
25 
25.192141 
31.28774952 
FALSE 
Figure 1519. Spreadsheet to estimate the area of the irregular polygon of Figure 1518.
There are 2000 rows of inside/outside calculation in the spreadsheet, (folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', sheet 'Area by Custom Function')
Now the blank cells, pleasing to the eye in the table but disastrous when used in a chart, are replaced by #NA! values, unpleasing in the table but perfect when used in a chart. To make the #NA! values "disappear," you can use Conditional Formatting. The conditional formatting formula applied to the cells in column D, beginning in cell D17, is =ISERROR(D17), which, when TRUE, sets the font color of the text in the cell to white, thus making the #NA! value invisible. A similar format was applied to the values in column E, beginning in cell E17. You can see the error values if you select the range of cells, as shown in Figure 1520.
The data in $D$17:$E$2016, when added to the chart as a new series, shows the inside points, as illustrated in Figure 1521.
A 
8 
0 
D 
E  
5 
X 
Y 
Calculation of area  
S 
27 
18 
if points 
# inside  
7 
9 
28 
2000 
623  
8 
5 
11 
fraction  
9 
31 
6 
0.3115  
10 
44 
27 
total area 
area  
11 
23 
20 
1750 
545.1  
12 
36 
30.5  
13 
22 
30.5  
14 
27 
18 (This row echoes the first set of coords)  
15 
(2000 random points between x = 0 and * = 50,y = 0 iirul y = 35}  
16 
Xa 
Ya 
Inside? 
X and Y tor 
plotting point 
17 
31.54968765 
19.50260778 
TRUE 
31.54968765 
19.50260778 
18 
43.10121712 
16.55383516 
FALSE  
19 
32.67162151 
19.80326911 
TRUE 
32.67162151 
19.80326911 
20 
36.71470616 
15.93267943 
TRUE 
36.71470616 
15.93267943 
21 
36.90754745 
16.47652055 
TRUE 
36,90754745 
16.47852055 
22 
44.32583965 
27 69863231 
FALSE  
23 
7.71297725 
24.09107738 
FALSE 
Figure 1520. Spreadsheet layout to estimate the area of an irregular polygon and to plot the random points within the polygon, (folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', sheet 'Area by Custom Function')
Figure 1520. Spreadsheet layout to estimate the area of an irregular polygon and to plot the random points within the polygon, (folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', sheet 'Area by Custom Function')
X coordinate
Figure 1521. Estimating the area of an irregular polygon, with, the "inside" random points shown.
(folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', sheet 'Area by Custom Function')
X coordinate
Figure 1521. Estimating the area of an irregular polygon, with, the "inside" random points shown.
(folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', sheet 'Area by Custom Function')
Responses

caragh fraser2 years ago
 Reply

beth6 months ago
 Reply