Latitude and Longitude Great Circle Formula in Excel

Here's a quick sample of how to calculate based on origin and destination longitute and latitude. I found this code quite some time ago.

Assuming the following:
A2 = Origin Latitude
B2 = Origin Longitude
C2 = Destination Latitude
D2 = Destination Longitude
E2 = calculation formula (see below)

sew

=IF(ISERROR(C2),"",IF(C2="",C2,3963*ACOS(COS(RADIANS(90-A2))*COS(RADIANS(90-C2))+
SIN(RADIANS(90-A2))*SIN(RADIANS(90-C2))*COS(RADIANS(B2-D2)))))

                                              

1:
2:

Here is a complete description of how, using Excel, to enter lat/long values and how to calculate distances:

http://www.cpearson.com/excel/latlong.htm

Kevin

Here is a more complete solution. Add the code below to any general code module. To use it, enter a formula like this to return the distance in kilometers between any two locations:

   =GetGreatCircleDistance(A1,B1,A2,B2)

where A1/B1 are the lat/long of the first location and A2/B2 are the lat/long of the second location. To return miles:

   =GetGreatCircleDistance(A1,B1,A2,B2,TRUE)

The code:

Public Function GetGreatCircleDistance( _
      ByVal Latitude1 As Variant, _
      ByVal Longitude1 As Variant, _
      ByVal Latitude2 As Variant, _
      ByVal Longitude2 As Variant, _
      Optional ByVal ReturnMiles As Boolean _
   ) As Double

' Return the distance between two locations expressed as latitude and
' longitude pairs. Result is returned as kilometers (default) or miles.
'
' Syntax
'
' GetGreatCircleDistance(Latitude1, Longitude1, Latitude2, Longitude2, [ReturnMiles])
'
' Latitude1, Longitude1 - The latitude and longitude of the first location.
'
' Latitude2, Longitude2 - The latitude and longitude of the second location.
'
' ReturnMiles - Pass True to return the distance in miles, False to return
'   Kilometers. Optional. If omitted then kilometers are returned.
'
' All latitude and longitude values can be decimal degrees (35.71916667) or
' text degrees minutes seconds values (35° 43' 9").

   Dim RadiansFactor As Double
   Dim Value As Double
   
   ' Degrees to radians conversion
   RadiansFactor = 3.14159265358979 / 180
   
   ' Normalize parameters
   If VarType(Latitude1) = vbString Then Latitude1 = GetDegreesFromDMS(Latitude1)
   If VarType(Longitude1) = vbString Then Longitude1 = GetDegreesFromDMS(Longitude1)
   If VarType(Latitude2) = vbString Then Latitude2 = GetDegreesFromDMS(Latitude2)
   If VarType(Longitude2) = vbString Then Longitude2 = GetDegreesFromDMS(Longitude2)
   Latitude1 = Latitude1 * RadiansFactor
   Longitude1 = Longitude1 * RadiansFactor
   Latitude2 = Latitude2 * RadiansFactor
   Longitude2 = Longitude2 * RadiansFactor
   
   ' Calculate the distance in kilometers or miles
   Value = Sin(Latitude1) * Sin(Latitude2) + Cos(Latitude1) * Cos(Latitude2) * Cos(Longitude2 - Longitude1)
   Value = Atn(-Value / Sqr(-Value * Value + 1)) + 2 * Atn(1)
   If ReturnMiles Then
      GetGreatCircleDistance = 3959 * Value
   Else
      GetGreatCircleDistance = 6378.136 * Value
   End If
   
End Function

Public Function GetDegreesFromDMS( _
      ByVal DegreesMinutesSeconds As String _
   ) As Double

' Return the decimal form of the degrees minutes seconds text value.
'
' Syntax
'
' GetDegreesFromDMS(DegreesMinutesSeconds)
'
' DegreesMinutesSeconds - A string representation of degrees minutes seconds
'   such as 35° 43' 9". Any part of the string can be omitted.
   
   Dim Tokens As Variant
   Dim Token As Variant
   
   Tokens = Split(DegreesMinutesSeconds)
   For Each Token In Tokens
      If Len(Token) > 0 Then
         Select Case Right(Token, 1)
            Case "°"
               GetDegreesFromDMS = GetDegreesFromDMS + CLng(Left(Token, Len(Token) - 1))
            Case "'"
               GetDegreesFromDMS = GetDegreesFromDMS + CLng(Left(Token, Len(Token) - 1)) / 60
            Case """"
               GetDegreesFromDMS = GetDegreesFromDMS + CLng(Left(Token, Len(Token) - 1)) / 60 / 60
         End Select
      End If
   Next Token

End Function

To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Kevin

Now that you have so many different ways to calculate the distance, the next trick is to find the best place to put your office. The tool to use for this purpose is Solver, an optional add-in in Excel.

I assumed an office latitude & longitude, then set up formulas calculating the distance to each customer. I then multiplied those distances by a weighting factor. The goal then becomes to choose the office latitude & longitude that gives the minimum sum of weighted distances.

To Solver, the problem is one of minimization. You have one cell (the weighted sum of distances) as the objective function. Solver's goal is to minimize its value by changing the office latitude & longitude. A sample workbook has been attached showing how this might work.

Since the Solver add-in is an optional install in all versions of Excel, you will probably need to install it. You'll need your Office CD-Rom for this purpose. But first, check to make sure that it is enabled (if it has already been installed):

1) Open Tools...Add-Ins menu item and check the Solver checkbox. You are now ready to use Solver (no need for steps 2 through 9 below)

If you didn't see Solver in the add-ins list in step 1:
2) Open the Start menu...Control Panel...Add or Remove Programs
3) Choose Microsoft Office, then click the "Change" button
4) In the next dialog, choose the "Add or Remove Features" option, then click the "Next" button
5) Check the box for "Advanced Customization of Applications" in the next dialog, then click the "Next" button
6) Find the Solver add-in. Its location varies, but in Office 2003 it is listed under Microsoft Office...Microsoft Office Excel...Add-Ins
7) Click the arrow to the right of the icon, and choose "Run from my computer"
8) Click the "Update" button, and be prepared to insert your Office CD-ROM
9) Once Office has finished updating, launch Excel and repeat step 1

If you want to read up on how Solver works, the add-in author has a pretty good tutorial at http://www.solver.com/tutorial.htm.