Convert Longitude & Latitude Start and End Coordinates into Nautical Miles (no API)

[zgoldflo]

I am trying to convert the START and END latitude and longitude coordinates I have into nautical miles. I read here that I had to convert my latitude and longitude (in degrees) to radians which I did by using the =RADIANS function. My situation is as follows:

I have a start and finish lat and long coordinates.

What I am trying to do is find the distance in miles between START and END.

Problem with conversion to RADIANS:

When entering the =RADIANS function for the START latitude and longitude I was able to get the RADIANS output (hopefully it is correct). However, with the END latitude and longitude, I get this format (above image) when inputting the same function! I don't know how to remedy the issue for the END latitude and longitude. What am I doing wrong here? I believe the data type for both START and END are in a numerical format and shouldn't affect the output of the function.

What I am really trying to solve is how to correctly utilize the =ACOS formula to get the distance in miles. I have not succeeded in finding clear-cut directions anywhere to get the answer that I need. I did review Thomas Blum's YouTube video here but that still didn't help me with my RADIANS problem and I wasn't transposing any data like he was in the video. I have read some of the directions and previous posts concerning this issue here but was not able to find an answer that would solve my scenario.

If anyone has simple clear-cut directions step-by-step on how to transform latitude and longitude coordinates into nautical miles I would greatly appreciate it!

Not sure how to input the Mini-sheet, hope I did that right.

2021_divvy-tripdata.xlsx
	H	I	J	K	L	M	N	O
1	start_station_name	start_station_name	start_station_name	start_station_name	start_lat	start_lng	end_lat	end_lng
2					41.98	-87.65	41.98	-87.66
3	State St & 33rd St	13216	MLK Jr Dr & 29th St	TA1307000139	41.834734	-87.625813	41.84205	-87.617
4	Lakeview Ave & Fullerton Pkwy	TA1309000019	Ritchie Ct & Banks St	KA1504000134	41.925858	-87.638973	41.90687	-87.6262
5	Kedzie Ave & Milwaukee Ave	13085			41.929528	-87.707899	41.92	-87.72
6	Montrose Harbor	TA1308000012	Montrose Harbor	TA1308000012	41.963982	-87.638181	41.96398	-87.6382
7	Western Ave & Howard St	527	Campbell Ave & Fullerton Ave	15648	42.01885833	-87.690022	41.92468	-87.6893
8	Montrose Harbor	TA1308000012	Lakefront Trail & Bryn Mawr Ave	KA1504000152	41.963982	-87.638181	41.98404	-87.6523
9	MLK Jr Dr & 29th St	TA1307000139	Calumet Ave & 33rd St	13217	41.842052	-87.617	41.8349	-87.6179
10	University Ave & 57th St	KA1503000071	Ellis Ave & 60th St	KA1503000014	41.791478	-87.599861	41.7851	-87.6011
11	Cityfront Plaza Dr & Pioneer Ct	13427	Lake Shore Dr & Ohio St	TA1306000029	41.890573	-87.622072	41.89257	-87.6145
12	Lake Shore Dr & Ohio St	TA1306000029	Wabash Ave & Grand Ave	TA1307000117	41.89257	-87.614492	41.89147	-87.6268
January_2021

 

[kweaver]

Read the info and an app;roach here: Convert

 

[Jeffrey Mahoney]

Here is something I created a few years ago. It might help

Calc Lat Lon Distance.xls
	B	C	D	E	F	G	H	I
2	Great Circle Distance
3	First Point (A)						Second Point (B)
4	Degrees:	48.36764777					Degrees:	48.367619
5	Minutes:						Minutes:
6	Seconds:						Seconds:
7	Latitude	48.368					Latitude	48.36761900
8	Degrees:	-117.2921845					Degrees:	-117.292185
9	Minutes:						Minutes:
10	Seconds:						Seconds:
11	Longitude	-117.292					Longitude	-117.292
24
25	Distance	Kilometers:	0.0032	Feet:	10.49081	Miles:	0.00199
26		Meters:	3.20			Nautical Miles:	0.00173
27
28
29	Method:
30	(1) Enter the degrees, minutes, and seconds on both the latitude & longitude for the first point (A)
31	(2) Enter the degrees, minutes, and seconds on both the latitude & longitude for the second point (B)
32	(3) Read the distance in the yellow cells - Remember that the longitude for the US is a negative value and is calculated automatically; latititude for the US is a positive value.
33	Estimated accuracy is about 656 feet over 31 miles
Great Circle Distance

Cell Formulas
Range	Formula
C7,I7	C7	=+((C6/60)+C5)/60+C4
C11,I11	C11	=+(((C10/60)+C9)/60+C8)
F25	F25	=+C23*60*6076.1154856
D25	D25	=1.852*60*C23
D26	D26	=+D25*1000
H25	H25	=+C23*60*1.150782
H26	H26	=+C23*60

 

[zgoldflo]

Thanks for the messages. To be honest, these both look incredibly complicated for me to follow along with. I am not a mathematics or trigonometry major. Also @Jeffrey Mahoney anyway I can get access to that excel file. I still don't see the =ACOS formula there that I think would help convert the data format coordinates I have in my sheet to miles. Excuse my ignorance.

 

[zgoldflo]

Thanks for the messages. To be honest, these both look incredibly complicated for me to follow along with. I am not a mathematics or trigonometry major. Also @Jeffrey Mahoney anyway I can get access to that excel file. I still don't see the =ACOS formula there that I think would help convert the data format coordinates I have in my sheet to miles. Excuse my ignorance.

Also, any solutions for the RADIANS issue I am encountering for my END lat and long coordinates?

 

[zgoldflo]

Here is something I created a few years ago. It might help

Calc Lat Lon Distance.xls
	B	C	D	E	F	G	H	I
2	Great Circle Distance
3	First Point (A)						Second Point (B)
4	Degrees:	48.36764777					Degrees:	48.367619
5	Minutes:						Minutes:
6	Seconds:						Seconds:
7	Latitude	48.368					Latitude	48.36761900
8	Degrees:	-117.2921845					Degrees:	-117.292185
9	Minutes:						Minutes:
10	Seconds:						Seconds:
11	Longitude	-117.292					Longitude	-117.292
24
25	Distance	Kilometers:	0.0032	Feet:	10.49081	Miles:	0.00199
26		Meters:	3.20			Nautical Miles:	0.00173
27
28
29	Method:
30	(1) Enter the degrees, minutes, and seconds on both the latitude & longitude for the first point (A)
31	(2) Enter the degrees, minutes, and seconds on both the latitude & longitude for the second point (B)
32	(3) Read the distance in the yellow cells - Remember that the longitude for the US is a negative value and is calculated automatically; latititude for the US is a positive value.
33	Estimated accuracy is about 656 feet over 31 miles
Great Circle Distance

Cell Formulas
Range	Formula
C7,I7	C7	=+((C6/60)+C5)/60+C4
C11,I11	C11	=+(((C10/60)+C9)/60+C8)
F25	F25	=+C23*60*6076.1154856
D25	D25	=1.852*60*C23
D26	D26	=+D25*1000
H25	H25	=+C23*60*1.150782
H26	H26	=+C23*60

Thanks for your help.

 

[zgoldflo]

Hey folks, I found the excel sheet from @cpearson. What an amazing resource. Working on that now. Thanks!