Finding the next closes airport from a set of GPS

问题:

I have a set of airport coordinates as defined here which I'm attempting to perform calculations on in excel.

I want to find the next closest airport for any given airports' coordinates.

---

The closest I've been able to get is the use of the formula :

=INDEX($A$1:$A$20,MATCH(MIN(SQRT(($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2)),SQRT(($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2),0),1)

as shown on this page.

---

However, this formula will provide the closest named location (i.e the airport) to a given set of coordinates.

How can I find the next closest airport?

回答1:

Hope this will help, copy the below formula in D2 with shif+ctrl+enter:

=INDEX($A$2:$A$19,MATCH(MIN(IF($B$2:$B$19&$C$2:$C$19=B2&C2,99999,ACOS(COS(RADIANS(90-$B$2:$B$19))*COS(RADIANS(90-B2))+SIN(RADIANS(90-$B$2:$B$19))*SIN(RADIANS(90-B2))*COS(RADIANS($C$2:$C$19-C2)))*6371)),IF($B$2:$B$19&$C$2:$C$19=B2&C2,99999,ACOS(COS(RADIANS(90-$B$2:$B$19))*COS(RADIANS(90-B2))+SIN(RADIANS(90-$B$2:$B$19))*SIN(RADIANS(90-B2))*COS(RADIANS($C$2:$C$19-C2)))*6371),0),1)

回答2:

Use the SMALL formula :

SMALL({array};Nth) and LARGE({array};Nth) will return respectively
the Nth smallest and largest value of a set!

your formula updated :

=INDEX($A$1:$A$20,MATCH(SMALL(SQRT(($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2),2),SQRT(($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2),0),1)