I know this thread is old but I have just found use for it. I had to use these formulas to plot the nearest neighbours of clustered coordinates ('actual') which were enclosed by the coordinates for the outline of a circle ('design'). In such a case i had to use ...
{=MIN(SQRT((abs(A3-$E$3:$E$18)^2)+abs((B3-$F$3:$F$18)^2)))}
so that neighbours behind the coordinate of interest (in the 'actual' column) were also considered.
Quote:
Originally Posted by flyswiftly
Just to record a worksheet formula only method to also identify the closest
coord,
=MATCH(1,--(C3=SQRT((a3-$e$3:$e$18)^2+(B3-$f$3:$f$18)^2)),0) as an array
also will return an index into the design array pointing to the closest
point. The formula would be entered into the d column.
Horst
"flyswiftly" wrote:
Actually, it's pretty easy to do with an array formula. It'll look something
like this:
{=MIN(SQRT((A3-$E$3:$E$18)^2+(B3-$F$3:$F$18)^2))} Where the column a is
your actual x, b is actual y, e is design x and f is design y. The formula
would go into column c and be copied down through all the actual pairs.
Expand the $e$3:$e$18 and $f$3:$f$18 to fit your entire design array. Use
CONTROL-SHIFT-ENTER when you finish putting in the formula without the
braces. "{}" The braces get added when you hit the control-shift-enter key.
What it does is find the distance from the given actual point to ALL design
points and gets the minumum of them.
Have fun....
Horst
"Bill" wrote:
I have two arrays of (x,y) coordinates. Each with roughly 500 pairs. Call
one "Design" and the other "Actual". I need to find the distance from each
Actual coordinate to it's closest Design coordinate. Problems: 1). There is
low probability that any of the Actual coordinates with exactly match the
Design coordinates. 2). The arrays are not ordered anywhere near to
coorespondence (ie. Design coord x1,y1 will not necessarily coorespond with
Actual coord x1,y1). 3). Some Design coordinates will not have any Actual
matches.
Difficult enough? ;)
|