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.

Calculate The Area Polygon Excel

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

Figure 15-18. Estimating the area of an irregular polygon.

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

Irregular Polygon Area Calculator

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')

Calculate The Area Polygon Excel

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?
    2 years ago
  • beth
    How to calculate irregular polygon area in excel?
    6 months ago

Post a comment