Formula to calculate distance between locations given Lat and Long

[leebauman]

My data consists of hundreds of Latitude and Longitude location coordinates; I need to know distance (in miles) from each location to the location which is closest and the location which is furthest away:

Excel 2010 32 bit

	E​	F​	G​	H​	I​
11​	Location​	Lat​	Long​	Distance to closest location​	Distance to furthest location​
12​	A​	34.4340498​	-119.8517606​
13​	B​	34.4388717​	-119.8086146​
14​	C​	34.4325943​	-119.8504486​
15​	D​	34.1693802​	-118.4502411​
16​	E​	34.1530838​	-118.2308807​
17​	F​	34.238292​	-118.561477​

Sheet: Sheet1

Is this possible?

I appreciate any help and suggestions. Thanks!

 

[Macropod]

Macropod, it appears the distances calculated for the 3rd and 4th locations in my data (Escondido, CA and Seattle, WA) are accurate; but the distances calculated for the 1st and 2nd locations (Pasadena, CA and Manhattan Beach, CA) are not accurate. Is it possible I'm doing something wrong in how I execute the formulas since they are consistent across the 4 outputs?

That seems probable. As I said, when I implement the formula using your coordinates, the Great Circle distance between Pasadena & Los Angeles works out to less than 14km.

Can the formula be enhanced so that it disregards coordinates in the reference set which are the same as the coordinates being considered?

For example, if I'm looking for min distance from Pasadena, CA to all locations in the reference set and Pasadena, CA happens to be in the reference set, I don't want the output to be 0 KM. In this case, I would want the output to be MIN distance from Pasadena to the next closest location.

To do that, you'd need to replace the MIN function with the SMALL function, using '2' as the index.

 

[leebauman]

That seems probable. As I said, when I implement the formula using your coordinates, the Great Circle distance between Pasadena & Los Angeles works out to less than 14km.

To do that, you'd need to replace the MIN function with the SMALL function, using '2' as the index.

Macropod, thank you for the response. Just one more query, if you'd be so kind....I have run the formula for both MIN and MAX and it produces the same output. I'm afraid I'm making a total amateur mistake when executing the formula; do I need to do something special like press SHIFT+ENTER or something similar to get this to work? I have recreated my own Sheet and entered the formula by typing it out in cells, but I just can't get it to work accurately consistently (for both MIN and MAX).

Just trying to make sure I'm not missing something obvious. My coordinates are in Geocode form, is that okay? I genuinely appreciate your assistance!

 

[Macropod]

I have run the formula for both MIN and MAX and it produces the same output. I'm afraid I'm making a total amateur mistake when executing the formula; do I need to do something special like press SHIFT+ENTER or something similar to get this to work?

Other than that they're array formulae, which require Ctrl-Shift-Enter?

 

[leebauman]

Other than that they're array formulae, which require Ctrl-Shift-Enter?

Yes!!! I knew it had to be something simple. Thank you for taking a moment to indulge my ignorance; this is a huge help to me.

I am grateful!

 

[leebauman]

That seems probable. As I said, when I implement the formula using your coordinates, the Great Circle distance between Pasadena & Los Angeles works out to less than 14km.

To do that, you'd need to replace the MIN function with the SMALL function, using '2' as the index.

Using the SMALL function and 2 or 3 or 4, etc as the index works great to provide me the nearest distances. Can I do something similar to know the name of the location which is 2nd closest, 3rd closest, etc? I've used the formula from the post you referenced and I can get the name of the nearest location, but I'm sufficiently ignorant enough to not be able to figure out how to find/change the index in the formula to produce the desired result of the name of the 2nd closest location, 3rd closest location, etc.

Many thanks for your consideration!

 

[Macropod]

Given that SMALL(X,2) gives the nearest, SMALL(X,3) gives the second nearest, and so on.

 

[leebauman]

Given that SMALL(X,2) gives the nearest, SMALL(X,3) gives the second nearest, and so on.

Right, I've used that logic to determine distance intervals between locations, but cannot make it work on the INDEX function which produces the location name which is closest, 2nd closest, 3rd closest and so on.

I've tried putting SMALL ahead of the INDEX operator and adding the ,2 or ,3 etc at the end of the argument, but it doesn't work. I realize it's probably a simple thing to do and I appreciate your consideration.

Thanks!

 

[Macropod]

You shouldn't need to do anything to the INDEX function - only to the MIN/SMALL function.

 

[leebauman]

Okay, I failed to notice the "MIN" hidden within the INDEX formula. I'll tinker with it and should be able to figure it out from here. Thanks again!

 

[Heronjeb]

Using the formulae I posted in the link, the distance I get between Pasadena & Los Angeles is 13.98km.

Hi,

I applied the given formula and it is giving almost same results to all cells and not correct.

LAT	LONG	Nearest distance km
46,05541167	14,50515853	2.260
46,08278359	14,51387535	2.261
46,0743663	14,4858691	2.261
46,05101796	14,50659327	2.259
46,05101796	14,50659327	2.259
46,05101796	14,50659327	2.259
46,06400286	14,50899856	2.260
46,06552	14,543091	2.260
46,065629	14,543439	2.260

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Formula: =MIN(ACOS(SIN(RADIANS(C2))*SIN(RADIANS($C$2:$C$524))+COS(RADIANS(C2))*COS(RADIANS($C$2:$C$524))*COS(RADIANS(C2-$D$2:$D$524))))*6367,465

Kindly ask for your prompt help.