Which function? Biggest distance to the Nearest city ...

[aneudul]

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

 

[Pete_UK]

Carrying on with your suggestion to use a 2-d table, with your data in
columns A, B and C starting on row 4 (insert blank rows above if
necessary), then <copy> the data, click on D1 and use Edit | Paste
Special | Transpose (check) | OK then <Esc> - this will give you a
copy of the towns and xy coordinates in the top 3 rows. Cell D4 will
thus represent the distance from town-1 to town-1, D5 from town-2 to
town-1 etc.

You can apply your formula to each cell, something like:

=(($B4-D$2)^2 +($C4-D$3)^2)^0.5

then copy this across and down. Then you can use MAX to find the
largest value, and possibly use it within an INDEX/MATCH function to
give you the names of the towns which are furthest apart.

Hope this helps.

Pete

 

[David Biddulph]

Carrying on with your suggestion to use a 2-d table, with your data in
columns A, B and C starting on row 4 (insert blank rows above if
necessary), then <copy> the data, click on D1 and use Edit | Paste
Special | Transpose (check) | OK then <Esc> - this will give you a
copy of the towns and xy coordinates in the top 3 rows. Cell D4 will
thus represent the distance from town-1 to town-1, D5 from town-2 to
town-1 etc.

You can apply your formula to each cell, something like:

=(($B4-D$2)^2 +($C4-D$3)^2)^0.5

....

Or, if you prefer, =SQRT(SUMSQ($B4-D$2,$C4-D$3))

 

[Bernard Liengme]

Sounds good to me.
Why not dist=SQRT(delta-x^2+delta-y^2)
Suppose you have the table in A1:I10 (city names in top row and left column)
Then =MAX(B210) will locate the largest distance
In B12, this formula =MAX(B2:B10) gives the max of first column
Copy this to I12
Now we need in B13, =MATCH(MAX(B12:I12),B12:I12,0)
This tells us the column in which the larges values lies
In C13 use =CHOOSE(B13+1,"A","B","C","D","E","F","G","H","I")&2
In D13 use =CHOOSE(B13+1,"A","B","C","D","E","F","G","H","I")&10
These give results like D2 and D12 (when the D column has the largest value)
In E13 use =MATCH(MAX(B1212),INDIRECT(C13&":"&D13),0)
Finally:=INDEX(A2:A10,B13) and =INDEX(B1:I1,E13-1) give the two city names
Let me know what mark we get for the homework
By the way his name was Pythagoras
best wishes

 

[Pete_UK]

Yes, David, but the OP put it in the form I used in his posting.

Pete

 

[vezerid]

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, 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

 

[Dana DeLouis]

=(($B4-D$2)^2 +($C4-D$3)^2)^0.5

Hi. I always mess up the relative references in a 2-d table.
To help me, I select each of the 2 Columns and give it a name. Same for
each of the two Rows.
Therefore, the equation stays the same throughout the table. Helps me
anyway.

=SUMSQ(x_2-x_1,y_2-y_1)^0.5

 

[aneudul]

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

Thank you everyone for your valuable contributions.
I'm still testing these options and I'm not sure which way I should
take; but one thing is sure: I'm learning along the way, thank you
again!
Thank you Pete, for the INDEX/MATCH trick; very interesting! (thanks
Vezerid for giving form to it)
Thank you David, for the SQRT/SUMSQ trick; I know that from now ...
Bernard, I'm afraid I need to read your solution once more ...
And no, this is not homework. The cities story is an allegory, I'm
actually dealing with coordinates in a 3-D color space; the "cities"
are color shades within that space. So I use Pythagoras (with h, thank
you Bernard) in three dimensions.

Now, Vezerid, I think it's brilliant what you said. I suspect you are
right so far; that way it's just a 1D-problem, and that simplifies a
lot the matter.
But I'm afraid that apparently I've been not clear enough in my
explanation of the problem, in this way:
Sorry if I'm wrong, but you all thought that I was looking for the
biggest distance between any two cities? No.
I repeat: I need to know which city is the most remote from its most
neighboring.
How can I clarify ... let's say that I want to choose the city with
the most green areas around it!
This way, we should use MIN() first to know, for each city, the
distance to its closest neighbour. Call it 'next-door-distance'?
Just after that we can use (maybe MAX) to know which city has the
biggest 'next-door-distance' (the most remote 'aside'-neighbour). Or
just sort the values decreasing.
For instance: Vezerid, if I use your proposal, and I replace MAX with
MIN, then I get all 0-values, since the reference city is included in
the matrix of 'neigbours' (and the distance to itself is of course 0).
So, how can I exclude the reference city (row 2 in your example) from
the formula?

I'm very grateful to you all, guys
Have a nice day

Marcos

 

[Pete_UK]

Ok, continuing with the 2-d table idea, suppose you have 5 cities and
your table is like this:

town_1 town_2 town_3 town_4 town_5
0 15 20 10 6
15 0 23 14 17
20 23 0 12 16
10 14 12 0 24
6 17 16 24 0

The first column shown will be column D, so in column J (leaving a
gap) you can put this array* formula in J4:

=MIN(IF(D4:H4>0,D4:H4))

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER to commit it,
rather than the normal ENTER. If you do this correctly then Excel will
wrap the formula in curly braces { } when viewed in the formula bar -
you must not type these yourself.

Then copy the formula into J5:J8, to give you 6, 14, 12, 10, 6. Then
you can apply your MAX formula to this to give you 14 as the most
remote pairing - is this the kind of thing you wanted?

Hope this helps.

Pete

 

[vezerid]

Marcos,

To get the city with the minimum distance you need one trick to
exclude the current city.

=MIN(IF($B$2:$B$20<>B2,(($B$2:$B$20-B2)^2+($C$2:$C$20-C2)^2)^0.5))

For the rest of your problem, lemme see if I understand. Let us say
you are considering color1. Now, first you find the shade nearest to
color1, let us say it is color5. Then you want to find, among all
colors, which color has the largest distance from color5? And report
this next to color1?

Clarify this and, if I have understood correctly, we can solve the
problem.

Kostis