# find if an x and y co-ordinate between another co -ordinate.

#### dilla1988

##### New Member
hi guys,

So i have about 1000s of lat and long values.

for this set of data I have drawn a boundary wall so i wanna see if these co-ordinates are within that boundary. if yes it's simple "YES" if not "NO"

so the boundaries are:
A -37.921484 ,144.983256
B-37.925141, 145.209269
C-38.035877, 144.980122
D -38.039549, 145.206486

boundary wall looks like:
A B
C D

just few of the co-ords are

 -37.6649 ,145.3983 -37.6584, 145.3972 -37.9827, 145.1018 -37.9846, 145.0923 -37.9849, 145.0999 -37.9842, 145.0986 -37.7262, 144.8926

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

So what's the best way to approach this? I'm getting a bit confused with the negative values.

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi,

First of all, your fence looks rather like:
 DB CA

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

Having noticed that, appropraiate approach could be:
find equations of two close to horizontal pieces of fence DB and CA as y1=a1*x+b1 and y2=a2*x+b2
and equations for vertical pieces DC and BA as x1= c1*y+d1 and x2= c2*y+d2

Then for each point check if y is between y1=a1*x+b1 and y2=a2*x+b2 for given x
and whether
x is between x1= c1*y+d1 and x2= c2*y+d2 for given y

If both conditions are met - (x,y) is within the fence

See sample formulas in http://klubexcela.pl/images/files/xls/Testfence.xls
of course formulas from columns D:F could be combined into one, so in D8 one could write a bit longer direct formula:
Code:
=IF(AND(B8>=C8*\$H\$5+\$I\$5,B8<=C8*\$H\$6+\$I\$6,C8>=B8*\$H\$2+\$I\$2,C8<=B8*\$H\$3+\$I\$3),"YES","NO")
Where B8 and C8 are coordinates of tested point, and in H2:I5 are located parameters a...d of fence lines

<tbody>
</tbody>

Consider a point X

If X is inside the quadrilateral, the Angle BAX has to be smaller than BAC
Similarly ACX < ACD, CDX < CDB and DBX < DBA

Hi Mike,
Sure, but it will probably (correct me if I'm wrong) need more computing, at least if we try (as I'd do) to find angles based on the law of cosines.
Code:
Angle = arccos ( (BX^2-BA^2-AX^2) / 2*BA*AX )
and Pythagoras' theorem to calculate the length of the AX BA and BX
Code:
Length = sqrt( (X2-X1)^2 + (Y2-Y1)^2 )

Hi Mike,
Sure, but it will probably (correct me if I'm wrong) need more computing, at least if we try (as I'd do) to find angles based on the law of cosines.
Code:
Angle = arccos ( (BX^2-BA^2-AX^2) / 2*BA*AX )
and Pythagoras' theorem to calculate the length of the AX BA and BX
Code:
Length = sqrt( (X2-X1)^2 + (Y2-Y1)^2 )

I was thinking more of using the inner product to find angles.
Cos(Angle ABC) = (A-B).(C-B)/Len(A-B)/Len(B-C)

mikerickson and Kaper, Thanks for your input appreciate it!

Replies
19
Views
2K
Replies
22
Views
2K
Replies
24
Views
3K
Replies
4
Views
790
Replies
1
Views
733

1,220,965
Messages
6,157,123
Members
451,399
Latest member
alchavar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back