search for x-y co-ordinates

[twin peaks]

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

 

[Bernie Deitrick]

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]

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]

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