Vertex42 The Excel Nexus
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')
Was this article helpful?