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


On Oct 12, 1:07 am, Pete_UK wrote:
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:H40,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

On Oct 11, 10:39 pm, aneudul wrote:

On 11 okt, 10:37, 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

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 su 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- Hide quoted text -

- Show quoted text -