Complex Distance Formula

[dkoucky]

Hi,

I am trying to find the nearest distance between many points. I have a set of about 300 coordinates that I am comparing to a list of about 4k coordinates. I am using the formula:

=IFERROR(ACOS(COS(RADIANS(90-VLOOKUP(V2,dist,4,FALSE)))*COS(RADIANS(90-O2))+SIN(RADIANS(90-VLOOKUP(V2,dist,4,FALSE)))*SIN(RADIANS(90-O2))*COS(RADIANS(VLOOKUP(V2,dist,5,FALSE)-P2)))*3959,99999999999)

The cells are explained below:

=IFERROR(ACOS(COS(RADIANS(90-LATITUDE 1)))*COS(RADIANS(90-LATITUDE 2))+SIN(RADIANS(90-LATITUDE 1)))*SIN(RADIANS(90-LATITUDE 2))*COS(RADIANS(LONGITUDE 1)-LONGITUDE 2)))*3959,99999999999)

This seems to work fine if comparing the list of 4k against the 300 but if I flip it around it just returns somewhat random numbers mostly 1 and 6, I assume this is somehow limited to the first x number of references? Is there another formula I can run or should I do this outside of excel?

Thanks!

 

[pbornemeier]

What do you mean by "flip it around"? Please explain your setup for 4K vs 300 and 300 vs 4K.

 

[pbornemeier]

300 x 4000 = 1,200,000
Implementing the comparison in code might be more efficient

What is the source of your formula? I was thinking https://en.wikipedia.org/wiki/Haversine_formula but that was not the case.

 

[dkoucky]

I think the formula is the great circle but it has been a while since I first used it and I have just been copying it. Do you know how/where I can use it in code? I am not sure if there is a way to host a file on this form I have uploaded the file via zippyshare here: https://www40.zippyshare.com/v/m72PpET4/file.html

 

[dkoucky]

Here is the same file from drive https://drive.google.com/file/d/1L3xoy-ArYMNR3L7wQHYQECtStFRMggdL/view?usp=sharing