Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
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 |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
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 On Oct 11, 9:37 am, aneudul wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
"Pete_UK" wrote in message
ups.com... 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)) -- David Biddulph |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
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(B2:D10) 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(B12:D12),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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "aneudul" wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
Yes, David, but the OP put it in the form I used in his posting.
Pete On Oct 11, 1:43 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: "Pete_UK" wrote in message ... 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)) -- David Biddulph |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
=(($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 -- Dana DeLouis "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... "Pete_UK" wrote in message ups.com... 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)) -- David Biddulph |
#8
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
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 |
#9
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
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 - |
#10
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
Which function? Biggest distance to the Nearest city ...
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which function? Biggest distance to the Nearest city ... | Excel Discussion (Misc queries) | |||
Finding state that contains employees biggest sale | Excel Discussion (Misc queries) | |||
zip code to city, state function | Excel Worksheet Functions | |||
Finding a name with biggest number | Excel Worksheet Functions | |||
Would u help me with a Biggest Movers type of comparison? | Excel Discussion (Misc queries) |