Minimum Distance Between Two Lat/Long Points

I'm struggling with a lat/long problem and was hoping someone would be willing to lend their mind. I have a list of airports and their lat/long coordinates. For each airport, I am looking to calculate the closest airport from the remainder of the list and return both the identifier of that airport and the distance between the two.

I have modeled this using {=Min(if(argument<>0,argument))} but the array formula absolutely kills my i5 processor. My model is using only 14 airports and my final product will need to use around 400. I've attached a sample xlsx to illustrate my work thus far. Also, I haven't been able to provide the identifier of that next nearest airport - only the distance between the two.

There must be a cleaner way to do this....

Thanks for any help!

CS

Right off the bat, your IF is way suboptimal.

IF(calc<>0, calc,"")
will calculate 2 * n -1 times; n to check for the IF, and then it will calc again the n-1 times the condition is true; it won't keep the calculation in memory because there's no instruction to do so.

Then it will do that for each of n columns.

So, n * (2n-1) = 2n^2 - n hefty calculations...
For 14 that would be 378 times, for 400 that would be 319,600 times.

I would move it into a triangular matrix to do it (n-1)^2 times. Won't duplicate comparisons that way.
14: 169
400: 159,201 times.

Try the attached on for size....

I'm not really sure the flop-count is any better for the actual calculation, because I'm feeding the calculation a lot of lookups; so the gain in the cheaper IF is probably wiped out by all those lookups. But array formulas are intensive.

Anyway I calculated with the spherical law of cosines in miles, so my numbers are different. Just change the calc to whatever method you want to use in the IF(column>row, CALC, "") that fills in the matrix.

Ben,

I can't thank you enough for that file... I wanted to keep the very simple look to it so I ended up sticking with the array method, but the formulas in your attached file opened some new doors and gave me enough ideas to finish a working copy. I've attached a small sample in case you, or anyone else, wanted to see the finished method. I won't be running the list very often so the 30 minutes it takes to process isn't a huge deal.

Thanks again,

Carl

very useful