closest neighbour coordinates

[Mick]

Hi gurus!

I have 20 named locations in a coordinates. Every point have x,y.

column a has the location name
column b has the x cooedinate
column c has the y coordinate

Now i have 400 coordinates in 2 columns (column e and f ) and want to
have the name of
the closest location (of the 20 named in column a) in column g.

I have tried with array formulas and Phytagoras - but no succes.

Help needed

Thanks in advance!

 

[somethinglikeant]

Hi,

i'd use the next 20 columns onwards from F to write formulas to
calculate the distance between the points detailed in E,F and your
location points. then pull out (maybe use a lookup function) the
closest location. As a start off the first function to go into cell G2
would be =SQRT((E2-$B$2)^2+(F2-$C$2)^2),
from the information you gave me.
You would have to copy this down to give you all the distances from
thwe first point co-ordinates in B,C.
Just repeat this but for the next column changing $B$2 to $B$3 and $C$2
to $C$3
repeat.

I know this is a little long winded but it will give you som every good
information from which you will be able to analyse.

Ant

 

[Mick]

Thanks - that does the trick

 

[Tom Ogilvy]

Assuming original data starts in A1, B1 and C1 with first locations in E1
and F1

If you don't want to fill your sheet with formulas, you can put this in G1
committed/entered with Ctrl+Shift+Enter rather than just enter since it is
an array formula, then drag fill it down the 400 rows.

=INDEX($A$1:$A$20,MATCH(MIN(SQRT(($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2)),SQRT(
($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2),0),1)

 

[Mick]

Hi Tom!
Simple SUPER! exactly what I was trying to do!
Do you have some sites on the net where arrayformulas is somehow
explained in a simple BUT good way
Thanks a LOT!
All the best!

 

[Tom Ogilvy]

You can start at Bob Phillip's page on Sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Although sumproduct doesn't have to be array entered (using
Ctrl+Shift+Enter) what Bob is explaining is its special use as an array
formula (it is really an array formula has the same limitations, etc).
Anyway, the concepts he explains are the same used a large subset of array
formula applications. Array formulas are much more robust - sumproduct only
handles a subset of array formula situations.

There is also a link there to Chip Pearson's page on array formulas:
http://www.cpearson.com/excel/array.htm

Bob Umlas's white paper:
http://www.emailoffice.com/excel/arrays-bobumlas.html