help with distance array

[grime]

I am calculating distances between 2 points.

Column A contains my 'X' location
Column B contains my 'Y' location
Column C contains the distance of the closest point:
{=SMALL(SQRT(POWER((A2-A2:A10),2)+POWER((B2-B2:B10),2)),2)}
Column D contains the distance to the 2nd closest point:
{=SMALL(SQRT(POWER((A2-A2:A10),2)+POWER((B2-B2:B10),2)),3)}
Column E contains the distance to the 3rd closest point:
{=SMALL(SQRT(POWER((A2-A2:A10),2)+POWER((B2-B2:B10),2)),4)}
and so on...

My problem is, although I can find the distances to those locations (rows), I can't figure out how to find which location is refers to.
So take column C for example...if my distance is 1.35...how do I find out the other location that the 1.35 came from?

Thanks in advance,

[Ron Rosenfeld]

On Fri, 30 Sep 2005 08:36:49 -0500, grime
<[email protected]> wrote:

>
>I am calculating distances between 2 points.
>
>Column A contains my 'X' location
>Column B contains my 'Y' location
>Column C contains the distance of the closest point:
>{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),2)}
>Column D contains the distance to the 2nd closest point:
>{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),3)}
>Column E contains the distance to the 3rd closest point:
>{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),4)}
>and so on...
>
>My problem is, although I can find the distances to those locations
>(rows), I can't figure out how to find which location is refers to.
>So take column C for example...if my distance is 1.35...how do I find
>out the other location that the 1.35 came from?
>
>Thanks in advance,

Something like:

x =INDEX(A:A,MATCH(1.35,C:C,0))
y =INDEX(B:B,MATCH(1.35,C:C,0))


--ron

[grime]

Thanks for the reply Ron, but that doesn't get me what I am looking for...

That gives me the coords of the original location... I need to find the coords for the location that it found to be the closest.

Basically, I'm looking at something similar to this, but it's not working:

{=IF(SQRT(POWER(($A2-$A$2:$A$888),2)+POWER(($B2-$B$2:$B$888),2))=SMALL(SQRT(POWER(($A2-$A$2:$A$888),2)+POWER(($B2-$B$2:$B$888),2)),2),ROW(A2:A898))}

[Bernie Deitrick]

Why

{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),2)}

and not

{=SMALL(SQRT(POWER((A2-H2:H10),2)+POWER((B2-I2:I10),2)),2)}

?

Anyway, you can use a variation of this array formula: (these are both for the fourth closest point)

To return the value from H:
=INDEX(H:H,MAX((SMALL(SQRT(POWER(($H2-$H$2:$H$10),2)+POWER(($I2-$I$2:$I$10),2)),4)=SQRT(POWER(($H2-$H$2:$H$10),2)+POWER(($I2-$I$2:$I$10),2)))*ROW($H$2:$H$10)))

To return the value from I:
=INDEX(I:I,MAX((SMALL(SQRT(POWER(($H2-$H$2:$H$10),2)+POWER(($I2-$I$2:$I$10),2)),4)=SQRT(POWER(($H2-$H$2:$H$10),2)+POWER(($I2-$I$2:$I$10),2)))*ROW($H$2:$H$10)))

HTH,
Bernie
MS Excel MVP


"grime" <[email protected]> wrote in message
news:[email protected]...
>
> I am calculating distances between 2 points.
>
> Column A contains my 'X' location
> Column B contains my 'Y' location
> Column C contains the distance of the closest point:
> {=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),2)}
> Column D contains the distance to the 2nd closest point:
> {=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),3)}
> Column E contains the distance to the 3rd closest point:
> {=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),4)}
> and so on...
>
> My problem is, although I can find the distances to those locations
> (rows), I can't figure out how to find which location is refers to.
> So take column C for example...if my distance is 1.35...how do I find
> out the other location that the 1.35 came from?
>
> Thanks in advance,
>
>
> --
> grime
> ------------------------------------------------------------------------
> grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
> View this thread: http://www.excelforum.com/showthread...hreadid=472086
>

[grime]

Works perfectly. Yer the man!