# 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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
1
Views
595
Replies
2
Views
376
Replies
2
Views
560
Replies
1
Views
700
Replies
7
Views
844

### Forum statistics

1,196,120
Messages
6,013,574
Members
441,774
Latest member
esandoval ### 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