search for x-y co-ordinates

  • Thread starter Thread starter twin peaks
  • Start date Start date
T

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
 
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
 
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
 
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
 
Back
Top