Comparing Latitude and Longitude to find Closest Distance

[one3nine0]

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

Company ID	State	Zip Code	Latitude	Longitude	Closest Company ID Distance (miles)
37	AK	99503	61.1988159	-149.9060107
80	AK	99518	61.17353626	-149.883512
230	AK	99654	61.56331423	-149.2925177
353	AK	99654	61.57614692	-149.4098359
270	AK	99669 <colgroup><col></colgroup><tbody> </tbody>	60.50016076	-151.0765804 <colgroup><col></colgroup><tbody> </tbody>
272	AK	99672 <tbody> </tbody>	60.52622641	-150.7265724 <tbody> </tbody>
81	AK	99701	64.836296	-147.715406

<tbody>
</tbody>

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