search for x-y co-ordinates
 

Hi folks
how do I search for the points which are very close together in an array of
points (represented x,y co-ordinates)?
Any help will be greatly appreciated

search for x-y co-ordinates
 

If your X coordinates start in cell A2, and your y coords in B2, then in C2
array enter (enter using Ctrl-Shift-Enter) the formula

=SMALL(SQRT((A2-$A$2:$A$???)^2+(B2-$B$2:$B$???)^2),2)

Replace the ??? with the row of your last XY pair.

Then copy down to match your XY list.

Then in D2, enter the formula

=IF(C2=MIN(C:C),"We're closest","")

and copy down to match. At least two cells will say "We're closest" (if
there is a tie, you may have 3 or 4 or more points that you will need to
pair up - but you could help with that by sorting your values by the X value
first).

HTH,
Bernie
MS Excel MVP



"twin peaks" wrote in message
...
Hi folks
how do I search for the points which are very close together in an array
of
points (represented x,y co-ordinates)?
Any help will be greatly appreciated

search for x-y co-ordinates
 

Thanks Bernie
Do not understand replace ??? with the row of your last XY pair.
Tried insert actual values or say, A5,B5 but don't work
Kindly explain


"Bernie Deitrick" wrote:

If your X coordinates start in cell A2, and your y coords in B2, then in C2
array enter (enter using Ctrl-Shift-Enter) the formula

=SMALL(SQRT((A2-$A$2:$A$???)^2+(B2-$B$2:$B$???)^2),2)

Replace the ??? with the row of your last XY pair.

Then copy down to match your XY list.

Then in D2, enter the formula

=IF(C2=MIN(C:C),"We're closest","")

and copy down to match. At least two cells will say "We're closest" (if
there is a tie, you may have 3 or 4 or more points that you will need to
pair up - but you could help with that by sorting your values by the X value
first).

HTH,
Bernie
MS Excel MVP



"twin peaks" wrote in message
...
Hi folks
how do I search for the points which are very close together in an array
of
points (represented x,y co-ordinates)?
Any help will be greatly appreciated



.

search for x-y co-ordinates
 

If you have XY data from rows 2 to 103, replace the ??? in the formula with
103:

=SMALL(SQRT((A2-$A$2:$A$103)^2+(B2-$B$2:$B$103)^2),2)

Bernie


"twin peaks" wrote in message
...
Thanks Bernie
Do not understand replace ??? with the row of your last XY pair.
Tried insert actual values or say, A5,B5 but don't work
Kindly explain


"Bernie Deitrick" wrote:

If your X coordinates start in cell A2, and your y coords in B2, then in
C2
array enter (enter using Ctrl-Shift-Enter) the formula

=SMALL(SQRT((A2-$A$2:$A$???)^2+(B2-$B$2:$B$???)^2),2)

Replace the ??? with the row of your last XY pair.

Then copy down to match your XY list.

Then in D2, enter the formula

=IF(C2=MIN(C:C),"We're closest","")

and copy down to match. At least two cells will say "We're closest" (if
there is a tie, you may have 3 or 4 or more points that you will need to
pair up - but you could help with that by sorting your values by the X
value
first).

HTH,
Bernie
MS Excel MVP



"twin peaks" wrote in message
...
Hi folks
how do I search for the points which are very close together in an
array
of
points (represented x,y co-ordinates)?
Any help will be greatly appreciated



.