## 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 15-15, 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 15-15. Determining whether a point lies inside or outside an irregular polygon.

X coordinate of point

Figure 15-15. 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 15-16) illustrates the calculation using worksheet formulas.

 A B C 0 E F 4 Does segment have... Both x Both x Both y Yc-Ya 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 15-16. Inside/outside determined by using worksheet formulas, (folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', sheet 'Single Point Diagram')

Figure 15-16. 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 15-16), 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 15-16 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+(B7-B6)*(\$A\$19-A6)/(A7-A6)-\$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 15-17. VBA code to determine inside/outside, (folder 'Chapter 15 Examples', workbook 'Inside or Outside Figure', module 'Module 1')

Figure 15-19 illustrates the use of the custom function to estimate the area of an irregular polygon such as the one shown in Figure 15-18. 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 15-19. But null-string 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 15-19. Spreadsheet to estimate the area of the irregular polygon of Figure 15-18.

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 15-20.

The data in \$D\$17:\$E\$2016, when added to the chart as a new series, shows the inside points, as illustrated in Figure 15-21.

 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 15-20. 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 15-20. 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 15-21. 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 15-21. 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')

0 -1

### Responses

• caragh fraser
What is an irregular polygon?
3 years ago
• beth
How to calculate irregular polygon area in excel?
1 year ago
• thomas eisenhower
How to calculate the volume of a 3d irregular polygon excel vba?
5 months ago
• ghenet
How can i calculate area of an irregular shape using excel?
3 months ago