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]

Did you input it as an array formula?

 

[cyrilbrd]

Book1
	C	D	E	F	G	H
17	LAT	LONG		nearest airport		Nearest distance km
18	34.83257949	-86.46106284		"Madison County Executive Airport-Tom Sharp Jr Field"		9.3660237
19	46.08278359	14.51387535		"Ljubljana Railway Station"		2.6564075
20	32.47986191	-85.64240964		"Moton Field Municipal Airport"		4.1297374
21	31.092847	-17.931806		"Funchal Cruise Terminal"		197.35917
22	11.95	-66		"La Orchila Airport"		25.18078
23	46.05101796	14.50659327		"Ljubljana Railway Station"		1.0100758
24	46.06400286	14.50899856		"Ljubljana Railway Station"		0.6496917
25	46.06552	14.543091		"Ljubljana Railway Station"		2.4282363
26	46.065629	14.543439		"Ljubljana Railway Station"		2.4574973
27	46.23676259	14.37318777		"Ljubljana Joe Pucnik Airport"		6.6534809
28	46.052361	14.514183		"Ljubljana Railway Station"		0.7318904
Sheet1

Cell Formulas
Range	Formula
H18	=ACOS(COS(RADIANS(90-C18)) * COS(RADIANS(90-INDEX(DB_loc[Latitude],MATCH(Sheet1!F18,DB_loc[Name],0)))) + SIN(RADIANS(90-C18)) * SIN(RADIANS(90-INDEX(DB_loc[Latitude],MATCH(Sheet1!F18,DB_loc[Name],0)))) * COS(RADIANS(D18-INDEX(DB_loc[Longitude],MATCH(Sheet1!F18,DB_loc[Name],0))))) * 6371
F18	{=INDEX(DB_loc[Name],MATCH(SMALL((ABS(C18-DB_loc[Latitude])^2+ABS(D18-DB_loc[Longitude])^2)^(0.5),1),(ABS(C18-DB_loc[Latitude])^2+ABS(D18-DB_loc[Longitude])^2)^(0.5),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cyrilbrd]

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	ID	Name	City	Country	IATA	ICAO	Latitude	Longitude	Altitude	Timezone	DST	Tz Database	Type
3	1	"Goroka Airport"	"Goroka"	"Papua New Guinea"	"GKA"	"AYGA"	-6.081689834590001	145.391998291	5282	10	"U"	"Pacific/Port_Moresby"	"airport"
4	10	"Thule Air Base"	"Thule"	"Greenland"	"THU"	"BGTL"	76.5311965942	-68.7032012939	251	-4	"E"	"America/Thule"	"airport"
5	100	"Ottawa Macdonald-Cartier International Airport"	"Ottawa"	"Canada"	"YOW"	"CYOW"	45.3224983215332	-75.66919708251953	374	-5	"A"	"America/Toronto"	"airport"
6	1000	"Faya Largeau Airport"	"Faya-largeau"	"Chad"	"FYT"	"FTTY"	17.91710090637207	19.111099243164062	771	1	"N"	"Africa/Ndjamena"	"airport"
DB

 

[Heronjeb]

Hi Macropod, yes I did. My formula is :

=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

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

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

 

[Macropod]

Well, aside from the fact you have a mistake in the formula (your final C2 should be D2) and that the last 3 locations in post 24 are the same, you're evidently trying to compare distances between cells within $C$2:$D$524 against themselves instead of against cells outside that range - as is clearly shown in the demo workbook in the linked thread. So, even if you fix the formula, you'll essentially get 0 (bear in mind my comments about accuracy at very small distances in the linked thread) for all rows because of the way you're doing things.