Which function? Biggest distance to the Nearest city ...
Having read the thread so far, as well as suspicions that it is a
homework assignment...
I doubt that the solution I am proposing would be for a homework or
that it could be used for one, IF I have understood the problem and
the OP's intentions correctly.
I don;t see the need for a 2D table. Such a table would be to measure
ALL distances. This seems a 1D problem, and we want the furthermost
city from a given city or its distance. Hence, assuming:
col A: city name
col B: x coords
col C: y coords
Data in A2:A20
For the largest distance, in D2 *array* formula:
=MAX((($B$2:$B$20-B2)^2+($C$2:$C$20-C2)^2)^0.5)
For the city with the largest distance. If using the distances in
column D:D, then in E2 *array* formula,
=INDEX($A$2:$A$20,MATCH(D2,((($B$2:$B$20-B2)^2+($C$2:$C$20-C2)^2)^0.5),
0))
Or, if we want to avoid the distance column, in E2:
=INDEX($A$2:$A$20,MATCH(MAX((($B$2:$B$20-B2)^2+($C$2:$C$20-C2)^2)^0.5),
((($B$2:$B$20-B2)^2+($C$2:$C$20-C2)^2)^0.5),0))
All these are array formulas, they must be committed with Shift+Ctrl
+Enter
HTH
Kostis Vezerides
On Oct 11, 11:37 am, aneudul wrote:
Hi everyone here,
Normally I'd keep thinking for a solution by myself, but I've
recognized my own limitations.
This is my (your) challenge: (I'll use a imaginary case, to make my
point clear)
I got a list of, let's say, cities and their location; this is, x-y
coordinates.
I need to know which city is the most remote from its most neighboring
city; kind of: biggest minimum distance.
I know Pitagoras to calculate the distance:
if city A x=5 y=8
and city B x=1 y=3
then distance AB= ( deltax ^2 + deltay ^2 ) ^0.5
in this case AB= (4^2+5^2)^0.5 = 6.403
So, I need to know the biggest distance between EACH cell in the list
and the other ones ...
I suspect that I need to make a two-dimensional cross-table, with the
same list as column headers and row headers? (sorry about my english,
I have a Dutch version of the software).
Am I searching in the right direction?
I would appreciate any help, since I need to resolve this issue very
soon.
Thanks in advance!
Marcos
|