Comparing Latitude and Longitude to find Closest Distance

[one3nine0]

So i have a table like the one below (but much larger, showing multiple states):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Company ID[/TD]
[TD]State[/TD]
[TD]Zip Code[/TD]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[TD]Closest Company ID Distance (miles)[/TD]
[/TR]
[TR]
[TD]37
[/TD]
[TD]AK[/TD]
[TD]99503[/TD]
[TD]61.1988159
[/TD]
[TD]-149.9060107
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]AK[/TD]
[TD]99518[/TD]
[TD]61.17353626
[/TD]
[TD]-149.883512[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230
[/TD]
[TD]AK[/TD]
[TD]99654
[/TD]
[TD]61.56331423
[/TD]
[TD]-149.2925177
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]353
[/TD]
[TD]AK[/TD]
[TD]99654
[/TD]
[TD]61.57614692
[/TD]
[TD]-149.4098359
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]270
[/TD]
[TD]AK[/TD]
[TD][TABLE="width: 42"]
<colgroup><col></colgroup><tbody>[TR]
[TD]99669[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]60.50016076
[/TD]
[TD][TABLE="width: 89"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]-151.0765804[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]272[/TD]
[TD]AK[/TD]
[TD][TABLE="width: 42"]
<tbody>[TR]
[TD]99672[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]60.52622641
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="align: right"]-150.7265724[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD]AK[/TD]
[TD]99701[/TD]
[TD]64.836296[/TD]
[TD]-147.715406[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there a formula or macro I can use that will give me the shortest distance to the nearest Company ID by Latitude and Longitude in an adjacent column?

 

[cyrilbrd]

Hi, yes you could use a formula to get the distance.
What would be the point's coordinates to compare with?

 

[one3nine0]

So the goal is to take this list, and for each company ID or location to find the nearest Company ID location and report the distance in the blank column.

I cannot find a way to make 1 formula to look and compare all the points to find the closest without having to individually compare each point and then just selecting the lowest value

The current distance formula I am working off of is: =ACOS(COS(RADIANS(90-Lat1)) * COS(RADIANS(90-Lat2)) + SIN(RADIANS(90-Lat1)) * SIN(RADIANS(90-Lat2)) * COS(RADIANS(Long1-Long2))) * 3958.761

 

[one3nine0]

Ok, I figured out how use an array to get what I need. I do get num errors with the ACOS function that I am not sure how to solve, and right now i am just performing those by name. Any insight into that would be really helpful

 

[cyrilbrd]

Try =0.621371*(RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(lat_1)-RADIANS(lat_2))/2)^2)+COS(RADIANS(lat_1))*COS(RADIANS(lat_2))*(SIN((RADIANS(long_1)-RADIANS(long_2))/2)^2))))))
withe the earth radius set at 6371.1 km