Latitude Longitude Conversion Functions
Andy Dulavitz
I am wondering if anyone out there has come up with custom number formats
for Lat and Long. as well as a function for converting string value to
decimal degrees.
I would like users to be able to enter data in the way they normally use it.
an example would look like;
Latitude Entry: 36-15.7N
Longitude Entry 075-19.3W
I would like to be able to enforce the entry of the E/W or N/S designator,
allow the use of either a - or a space to separate degrees from minutes,
allow the use of a leading zeros for longitude (add zeros if not entered),
and finally use a string function to extract and convert the enetered values
to decimal degrees with N and E displayed as a positive number and E and W
displayed as negative.
I have searched extensively and found all of the usual solutions. My best
solution to date has been to use separate cells for enrty of Deg, Min, and
the direction designator. A formula in a hidden column converts these to
decimal degrees. I have a workbook that computes voyage plans for the ship I
work on. I have written custom VBA functions that compute Mercator Sailing
solutions for Cse and Dist. I will galdly share these with anyone who wants
them. Again any help greatly appreciated.
A. Dulavitz
David McRitchie
Latitude And Longitude In Excel
http://www.cpearson.com/excel/latlong.htm
Entry is as (same as for time)
DD:MM:SS
formatted as
[h]:mm:ss
can be formatted as
hh°mm'ss\"
You won't be able to do what you want strictly
with cell formatting.
> Latitude Entry: 36-15.7N
> Longitude Entry 075-19.3W
Chip's page does not show the formatting as you want, you
might also try a Google web search: ( 1,370 entries)
latitude +longitude +excel +spreadsheet +N
Or perhaps better a Google Groups search: (42 entries)
with the same arguments, if this does break up on lines try:
http://groups.google.com/groups?as_q=latitude%20%2blongitude%20%2bexcel%20%20%2bspreadsheet%20%2bN
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Andy Dulavitz" <adul...@interconnect.net> wrote in message news:OjOgrjpoBHA.2528@tkmsftngp04...
Andy Dulavitz
Thanks for the help!!! I didn't find what I was looking for, but hopefully
someone will come forewad with an answer. I know that several commercial
navigation programs written of course in other envioroments handle this
issue quite well. Dealing "gracefully" with angular mathematics and the lack
of any geo functions seems to have been an oversight on Microsofts part. The
most common complaint I get from the users of my workbook is that they want
to enter thier data in the same fashion that they work with with it in other
media.
Also, for anyone interested I will share my Functions for Mercator sailing
below:
I hope that someone finds this useful, feel free to share/improve.
Thanks and Regards
Andy
Public Function MercatorCse(Lat1, Lon1, Lat2, Lon2 As Double)
' Lat1, Lon1 - lat and lon for position 1
' Lat2, Lon2 - lat and lon for position 2
' Returns Course in degrees by mercator sailing
' from position 1 to position 2
' Assumes input is North = + , East = +
' Declare Variables
Dim l As Double
Dim Dlo As Double
Dim NS As String
Dim EW As String
Dim m1 As Double
Dim m2 As Double
Dim m As Double
Dim Lon1Sin As String
Dim Lon2Sin As String
Dim LongIndicator As String
' Determine if Cse is due north or due south
If (Lon1 = Lon2) Then
If (Lat1 < Lat2) Then
Bearing = 0#
Else
Bearing = 180#
End If
' If Bearing is either due N or due S then go straight to end
GoTo SkipCalcs
' If not then we continue on with calculations
Else
' Assign E/W Direction Sign for Lon1 and Lon2
If Lon1 < 0 Then
Lon1Sin = "W"
Else
Lon1Sin = "E"
End If
If Lon2 < 0 Then
Lon2Sin = "W"
Else
Lon2Sin = "E"
End If
' Convert Longitudes to absolute numbers
Lon1 = Application.WorksheetFunction.Fixed(Abs(Lon1))
Lon2 = Application.WorksheetFunction.Fixed(Abs(Lon2))
' DETERMINE which of 4 cases we have for longitude, and
' CALCULATE Dlo and DETERMINE E/W componant of Cse Angle
LongIndicator = Lon1Sin & Lon2Sin
Select Case LongIndicator
Case "EW"
Dlo = Lon1 + Lon2
If Dlo > 180 Then
Dlo = 360 - Dlo
EW = "E"
Else
EW = "W"
End If
Case "WE"
Dlo = Lon1 + Lon2
If Dlo > 180 Then
Dlo = 360 - Dlo
EW = "W"
Else
EW = "E"
End If
Case "WW"
If Lon2 > Lon1 Then
Dlo = Lon2 - Lon1
EW = "W"
Else
Dlo = Lon1 - Lon2
EW = "E"
End If
Case "EE"
If Lon2 > Lon1 Then
Dlo = Lon2 - Lon1
EW = "E"
Else
Dlo = Lon1 - Lon2
EW = "W"
End If
End Select
' Multiply Dlo by 60 for use in further Calculations
Dlo = Dlo * 60
' CALCULATE MERIDIONAL PARTS:
' CALCULATION NOTES..........
' Formula for meridional parts is
' m = 7915.7 Log Tan (45 + (Lat/2) - 23 * Sin(Lat)
' Used a constant value of 0.017453 for converting degrees to Radians.
' Used a constant of 0.785398 for 45 in Radians
' Must divide Log of number by Log(10).
' Example: Log(Number) / Log(10)
m1 = 7915.7 * Log(Tan(0.785398163 + (Lat1 / 2) * 0.017453293)) /
Log(10) - (Sin(Lat1 * 0.017453293) * 23)
m2 = 7915.7 * Log(Tan(0.785398163 + (Lat2 / 2) * 0.017453293)) /
Log(10) - (Sin(Lat2 * 0.017453293) * 23)
m = m1 - m2
m = Application.WorksheetFunction.Fixed(Abs(m))
' CALCULATE COURSE ANGLE:
' The formula from Bowditch is ... Tan C = Dlo/m
If Lat1 = Lat2 Then
CseAngle = 90
GoTo Skip3
End If
CseAngle = (Dlo * 0.07453) / (m * 0.07453)
CseAngle = Atn(CseAngle)
' Convert to degrees
CseAngle = Application.WorksheetFunction.Degrees(CseAngle)
Skip3:
' Set Decimal places to 1
CseAngle = Application.WorksheetFunction.Fixed(CseAngle, 1)
' Make it a postive number so that the math works properly
CseAngle = Abs(CseAngle)
'________________________________________________________________
'
' NOTE: Had to use Worksheet Functions for DEGREES and FIXED as
' VBA does not have an equivilant
'________________________________________________________________
'
' CALCULATE THE COURSE ANGLE:
' Determine if North or South
If Lat2 > Lat1 Then
NS = "N"
Else
NS = "S"
End If
' Determine how to apply the course angle
If NS = "N" And EW = "E" Then
Bearing = 0 + CseAngle
End If
If NS = "S" And EW = "E" Then
Bearing = 180 - CseAngle
End If
If NS = "N" And EW = "W" Then
Bearing = 360 - CseAngle
End If
If NS = "S" And EW = "W" Then
Bearing = 180 + CseAngle
End If
' FINALLY WE OUTPUT THE RESULT:
End If
SkipCalcs:
MercatorCse = Bearing
Exit Function
End Function
Public Function MercatorDist(Lat1, Lon1, Lat2, Lon2, Cse)
Select Case Cse
Case 90
Dlo = Lon1 - Lon2
MercatorDist = Dlo * 60 * Cos(Lat1 * 0.017453)
Case 270
Dlo = Lon1 - Lon2
MercatorDist = Dlo * 60 * Cos(Lat1 * 0.017453)
Case 0
MercatorDist = Abs((Lat1 - Lat2) * 60)
Case 180
MercatorDist = Abs((Lat1 - Lat2) * 60)
Case Else
Rcse = 0.017453 * Cse
l = Abs((Lat1 - Lat2) * 0.017453)
MercatorDist = l / Cos(Rcse)
MercatorDist = Application.WorksheetFunction.Degrees(MercatorDist)
MercatorDist = Abs(MercatorDist) * 60
End Select
End Function