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]

See: trying to find a way to show a closest to using lat and long coordinates and the workbook attached to post #14 in that thread. For the maximum distances, simply change MIN to MAX!

 

[leebauman]

See: trying to find a way to show a closest to using lat and long coordinates and the workbook attached to post #14 in that thread. For the maximum distances, simply change MIN to MAX!

Perfect! Thanks!!

 

[leebauman]

See: trying to find a way to show a closest to using lat and long coordinates and the workbook attached to post #14 in that thread. For the maximum distances, simply change MIN to MAX!

I must be doing something wrong because the results are not accurate:

My reference data:
Excel 2010 32 bit

	A​	B​	C​
1​	Latitude​	Longitude​	Location​
2​	32.71569​	-117.16172​	San Diego, CA
3​	33.12316​	-117.08217​	Escondido, CA
4​	33.16588​	-117.33822​	Carlsbad, CA
5​	34.05349​	-118.24532​	Los Angeles, CA
6​	33.44825​	-112.07580​	Phoenix, AZ
7​	40.78200​	-73.83170​	New York, NY
8​	48.85693​	2.34120​	Paris, France
9​	51.50642​	-0.12721​	London, United Kingdom
10​	36.17191​	-115.13997​	Las Vegas, NV

Sheet: Well

My data with output in Kilometers (MIN distance between location and all closest location from reference data):
Excel 2010 32 bit

	A​	B​	C​	D​
1​	Location​	Latitude​	Longitude​	Distance in KM​
2​	Pasadena, CA	34.14745​	-118.14427​	183​
3​	Manhattan Beach, CA	33.88715​	-118.41084​	150​
4​	Escondido, CA	33.12316​	-117.08217​	24​
5​	Seattle, WA	47.60357​	-122.32945​	1544​

Sheet: Sheet1

My data with formulas:
Excel 2010 32 bit

	A​	B​	C​	D​
1​	Location​	Latitude​	Longitude​	Distance in KM​
2​	Pasadena, CA	34.1474494934082​	-118.144271850585​	=MIN(ACOS(SIN(RADIANS(B2))*SIN(RADIANS(Well!A$2:Well!A$10))+COS(RADIANS(B2))*COS(RADIANS(Well!A$2:Well!A$10))*COS(RADIANS(C2-Well!B$2:Well!B$10))))*6367.465​
3​	Manhattan Beach, CA	33.887149810791​	-118.410842895507​	=MIN(ACOS(SIN(RADIANS(B3))*SIN(RADIANS(Well!A$2:Well!A$10))+COS(RADIANS(B3))*COS(RADIANS(Well!A$2:Well!A$10))*COS(RADIANS(C3-Well!B$2:Well!B$10))))*6367.465​
4​	Escondido, CA	33.1231613159179​	-117.082168579101​	=MIN(ACOS(SIN(RADIANS(B4))*SIN(RADIANS(Well!A$2:Well!A$10))+COS(RADIANS(B4))*COS(RADIANS(Well!A$2:Well!A$10))*COS(RADIANS(C4-Well!B$2:Well!B$10))))*6367.465​
5​	Seattle, WA	47.6035690307617​	-122.329452514648​	=MIN(ACOS(SIN(RADIANS(B5))*SIN(RADIANS(Well!A$2:Well!A$10))+COS(RADIANS(B5))*COS(RADIANS(Well!A$2:Well!A$10))*COS(RADIANS(C5-Well!B$2:Well!B$10))))*6367.465​

Sheet: Sheet1

Closest location in the reference data to Pasadena, CA is Los Angeles, CA and it is less than 20 Kilometers (not 183 as indicated by the output here). Any ideas where I might have gone wrong?

Huge appreciation for any help or suggestions.

Thanks!

 

[mole999]

are the values 10 times greater ? 183 instead of 18.3, a simple between doesn't allow for the curvature of the earth

 

[Macropod]

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

 

[mole999]

Googlemaps
19 min (10.8 miles) AKA 17.38092Km and less as the crows flies
via CA-110 N
17 min without traffic
Los Angeles, CA
USA
Get on US-101 N from N Broadway
3 min (0.7 mi)
Follow CA-110 N/Pasadena Fwy to E Glenarm St in Pasadena
11 min (8.5 mi)
Take S Marengo Ave to E Holly St
6 min (1.6 mi)
Pasadena, CA
USA

 

[Macropod]

The distances the formula in my link calculate are Great Circle distances.

 

[leebauman]

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?

I am grateful for your expertise and assistance. Thanks!

 

[leebauman]

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.

Example:
Excel 2010 32 bit

	E​	F​	G​	H​	I​
1​	Location​	Latitude​	Longitude​	Min Distance​	Max Distance
2​	Pasadena, CA	34.14744949​	-118.1442719​	?​	?​

Sheet: Sheet1

Location Reference List:
Excel 2010 32 bit

	A​	B​	C​
1​	Location​	Latitude​	Longitude​
2​	San Diego, CA	32.715691​	-117.1617203​
3​	Pasdena, CA	34.147449​	-118.1442719​
4​	Long Angeles, CA	34.05349​	-118.2453232​
5​	San Francisco, CA	37.777119​	-122.4196396​

Sheet: Sheet1