For those who are in a rush for the solution and don't need all the background information, jump to the longitude latitude code. Or download the calculation workbook, and enter your longitude and latitude on the Excel spreadsheet.

longitude and latitude workbook

More Latitude and Longitude Resources

The search for an accurate solution to this problem has led me to numerous sites and attempted solutions. A long list of related sites is included at the end of all of this, but the most crucial to what I've found to be the current end of the road are these:

-- Lost Species

-- Long Lat Vincenty

-- Programming Book

along with

-- Inverse

-- Vincenty's Formulae

Accuracy is Important

A formula that is accepted to provide results that are accurate to within millimeters is known as Vincenty's formula. Naturally the accuracy of the results depends in large part on the accuracy of the latitude/longitude pairs describing the two points.

Why all the fuss over accuracy? Well, from what I've seen of other formulas, especially those written as a single worksheet function, their values differ quite a bit for what might be considered 'life critical' situations.

Typically they are short by some number of meters, typically about 20 to 30 feet per statute mile, and after flying just 30 or 40 miles, from my personal experience, I wouldn't care to land several hundred feet short of the approach end of a runway, much less be off by over 7 miles on a trip between Los Angeles and Honolulu.

Since the general tendency in dealing with these types of calculations is to "measure it with a micrometer, mark it with chalk and cut it with an axe", what we have here is a very precise micrometer to begin the measuring process with.

Excel Formulas for Distance

There are numerous Excel worksheet functions that will return an initial heading from one point to the destination point for a Great Circle path between them and similar formulas to return the distance between them.

But based on experience using them and comparing them to known measured distances, the Vincenty method of calculating the distance between the points has not been translated into a single Excel worksheet function, and very likely cannot be at least not easily.

Because it relies on reiterative calculations to deal with points that are very close to being on the exact opposite sides of the world, implementing it as even a series of Excel worksheet formulas is a daunting task.

Creating Excel Code for Distance

The basis for the solution presented by T. Vincenty can be found here:

Inverse

During my search I first found the movable-type.co.uk page which presented the coded solution in JavaScript. That didn't do me much good as far as coming up with an Excel VBA solution; so I continued the search and finally found the lost-species.livejournal.com page!!

I thought the search was over. Almost, but not quite.

When I moved that code into an Excel code module I was confronted with two sections that Excel not so politely informed me were "too complex" for it to evaluate. I managed to break those down into simpler statements that Excel could deal with and that did not corrupt the final results. That is the sum total of my contribution to the function provided below. I claim nothing more than that small contribution.

Working With Angles in Excel

But before using the function, there are a few preliminary steps that must be considered. Most significant is that Excel and VB works with angles expressed in radians, not as decimal values of the angles, nor from their initial "plain English" representation.

Consider this situation:

You have a Latitude represented as 10° 27' 36" S (10 degrees, 27 minutes 36 seconds South)

You need to get that into radians, and there is not a direct way to do it, before we can get it to radians it has to be converted into a decimal representation. We need to see it as: 10.46 which is the decimal equivalent to 10° 27' 36" and we need to take into consideration whether it is a North or South latitude, with South latitudes being treated as negative numbers.

Luckily Microsoft provides a couple of handy functions to convert standard angular notations to their decimal equivalent, and back, at this page:

Microsoft Functions

Those routines are included at the code section and one of them has a change made by me to permit you to make a regular angle entry as

10~ 27' 36" S instead of 10° 27' 36" S

because ~ is directly accessible from the keyboard, while the ° is not.

Converting to Radians

After converting the standard notation to a decimal value, we still have to convert that to radians and deal with the sign of the radian result.

The routines and formulas here consider negative latitudes to be South latitudes and negative longitudes to be West longitudes. While this may seem unfair to those of us living in the western hemisphere, what can I say other than deal with it!?

A decimal degree value can be converted to radians in several ways in Excel and for this process, a simple function is used that is also included in the code presented later.

The basic formula is

radians = angleAsDecimal x (Pi / 180)

where Pi is 3.14159265358979

The Final Solution

Copy all of the code below and paste it into a regular code module in your workbook. Instructions for placing the code into a regular module are here: Copy Excel VBA Code to a Regular Module

Set up your worksheet to pass the latitudes and longitudes of the start and end points as standard entries, then enter a formula to pass them to function distVincenty().

Note: If your coordinates are decimal numbers, see formulas in the Decimal Longitude Latitude section.

Given two points with these Latitude and Longitude coordinates:

Point 1:

Latitude: 37° 57' 3.7203" S
Longitude: 144° 25' 29.5244" E

Point 2:

Latitude: 37° 39' 10.1561" S
Longitude: 143° 55' 35.3839" E

The general format of the function call is:

=distVincenty( Pt1_LatAsDecimal, Pt1_LongAsDecimal, Pt2_LatAsDecimal, Pt2_LongAsDecimal)

A raw formula would look like this [Note the double-double quotes after the seconds entries]. The SignIt() function, provided as part of the code, converts a standard angular entry to signed decimal value.

=distVincenty(SignIt("37° 57' 3.7203"" S "), SignIt("144° 25' 29.5244"" E"), SignIt("37° 39' 10.1561"" S"), SignIt("143° 55' 35.3839"" E"))

You can use the ~ symbol instead of the ° symbol if it makes it easier for you:

=distVincenty(SignIt("37~ 57' 3.7203"" S "), SignIt("144~ 25' 29.5244"" E"), SignIt("37~ 39' 10.1561"" S"), SignIt("143~ 55' 35.3839"" E"))

If the coordinates for Point 1 are in B2 and C2 and the coordinates for Point 2 are in B3 and C3, then it could be entered as

=distVincenty(SignIt(B2), SignIt(C2), SignIt(B3), SignIt(C3))

The result for the 2 sample points used above should be 54972.271, and this result is in Meters.

Decimal Longitude Latitude

If your longitude and latitude are in decimal numbers, instead of degrees, use the formulas from the DecimalLatLong sheet in the sample file. On the worksheet,

Coordinates for Point 1 are in B2 and C2
Coordinates for Point 2 are in B3 and C3
Coordinates are entered as decimal numbers

longitude and latitude workbook in decimals

The following formulas are in row 5, and in these formulas, all of the SignIt functions have been removed.

The SignIt function converts degree entries to decimal numbers, and isn't needed in this case.

Here are the formulas for degree coordinates:

Cell B5: =distvincenty(B2,C2,B3,C3)
Cell D5: =MOD(DEGREES(ATAN2(COS(B2*PI()/180) *SIN(B3*PI()/180)-SIN(B2*PI()/180) *COS(B3*PI()/180) *COS(C3*PI()/180-C2*PI()/180), SIN(C3*PI()/180-C2*PI()/180) *COS(B2*PI()/180)))+360,360)

Problems Encountered

The initial code that I began working with generated "formula too complex" errors in two statements. These statements were initially broken into two pieces and then those two separate calculations were "rejoined" in a formula to obtain their final result without the "formula too complex" error.

During the preparation of this document, the package was tested in Excel 2010 64-bit and began returning #VALUE! errors for all input values. Investigation determined that a portion of the already split formula to determine the deltaSigma value was generating an overflow error. This error did not occur in the 32-bit version of Excel 2010, nor in Excel 2003 (a 32-bit application).

The offending line of code was once again broken down into smaller pieces that were eventually rejoined in a mathematically correct process that resulted in the proper values being determined without resorting to any alteration of the original algorithm at all.

These sections are noted in the comments in the code for Function distVincenty() below.

For any questions regarding all of this, contact Jerry Latham at HelpFrom@JLathamSite.com.

The Longitude Latitude Code

Now, at last, the code:

'*****************************************
Private Const PI = 3.14159265358979
Private Const EPSILON As Double _
    = 0.000000000001
'======================================
Public Function distVincenty(ByVal _
  lat1 As Double, ByVal lon1 As Double, _
    ByVal lat2 As Double, _
      ByVal lon2 As Double) As Double
'INPUTS: Latitude and Longitude of
'  initial and destination points
'  in decimal format.
'OUTPUT: Distance between the
'  two points in Meters.
'
'=============================
' Calculate geodesic distance (in m)
'  between two points specified by
'  latitude/longitude (in numeric
'  [decimal] degrees)
' using Vincenty inverse formula
'  for ellipsoids
'==============================
' Code has been ported by lost_species
'  from www.aliencoffee.co.uk to VBA
'  from javascript published at:
' https://www.movable-type.co.uk/scripts
'  /latlong-vincenty.html
' * from: Vincenty inverse formula -
'  T Vincenty, "Direct and Inverse
'  Solutions of Geodesics on the
' *       Ellipsoid with application
'  of nested equations", Survey Review,
'  vol XXII no 176, 1975
' *       https://www.ngs.noaa.gov/
'               PUBS_LIB/inverse.pdf
'Additional Reference:
'  https://en.wikipedia.org/wiki/
'      Vincenty%27s_formulae
'=============================
' Copyright lost_species 2008 LGPL
'  https://www.fsf.org/licensing/
'      licenses/lgpl.html
'=============================
' Code modifications to prevent
'  "Formula Too Complex" errors in
'  Excel (2010) VBA implementation
' provided by Jerry Latham,
'  Microsoft MVP Excel, 2005-2011
' July 23 2011
'=============================

  Dim low_a As Double
  Dim low_b As Double
  Dim f As Double
  Dim L As Double
  Dim U1 As Double
  Dim U2 As Double
  Dim sinU1 As Double
  Dim sinU2 As Double
  Dim cosU1 As Double
  Dim cosU2 As Double
  Dim lambda As Double
  Dim lambdaP As Double
  Dim iterLimit As Integer
  Dim sinLambda As Double
  Dim cosLambda As Double
  Dim sinSigma As Double
  Dim cosSigma As Double
  Dim sigma As Double
  Dim sinAlpha As Double
  Dim cosSqAlpha As Double
  Dim cos2SigmaM As Double
  Dim C As Double
  Dim uSq As Double
  Dim upper_A As Double
  Dim upper_B As Double
  Dim deltaSigma As Double
  Dim s As Double ' final result,
'  will be returned rounded to
'  3 decimals (mm).
'added by JLatham to break up
'  "Too Complex" formulas
'into pieces to properly calculate
'  those formulas as noted below
'and to prevent overflow errors when
'  using Excel 2010 x64 on
'  Windows 7 x64 systems
  Dim P1 As Double ' used to calculate
'  a portion of a complex formula
  Dim P2 As Double ' used to calculate
'  a portion of a complex formula
  Dim P3 As Double ' used to calculate
'  a portion of a complex formula

'See https://en.wikipedia.org/wiki
'  /World_Geodetic_System
'for information on various Ellipsoid
'  parameters for other standards.
'low_a and low_b in meters
' === GRS-80 ===
' low_a = 6378137
' low_b = 6356752.3141
' f = 1 / 298.257222101
'
' === Airy 1830 ===  Reported best
'  accuracy for England
'  and Northern Europe.
' low_a = 6377563.396
' low_b = 6356256.910
' f = 1 / 299.3249646
'
' === International 1924 ===
' low_a = 6378388
' low_b = 6356911.946
' f = 1 / 297
'
' === Clarke Model 1880 ===
' low_a = 6378249.145
' low_b = 6356514.86955
' f = 1 / 293.465
'
' === GRS-67 ===
' low_a = 6378160
' low_b = 6356774.719
' f = 1 / 298.247167

'== WGS-84 Ellipsoid Parameters ===
  low_a = 6378137       ' +/- 2m
  low_b = 6356752.3142
  f = 1 / 298.257223563
'=========================
  L = toRad(lon2 - lon1)
  U1 = Atn((1 - f) * Tan(toRad(lat1)))
  U2 = Atn((1 - f) * Tan(toRad(lat2)))
  sinU1 = Sin(U1)
  cosU1 = Cos(U1)
  sinU2 = Sin(U2)
  cosU2 = Cos(U2)

  lambda = L
  lambdaP = 2 * PI
  iterLimit = 100 ' can be set
'  as low as 20 if desired.

  While (Abs(lambda - lambdaP) > _
      EPSILON) And (iterLimit > 0)
    iterLimit = iterLimit - 1

    sinLambda = Sin(lambda)
    cosLambda = Cos(lambda)
    sinSigma = Sqr(((cosU2 * sinLambda) _
        ^ 2) + ((cosU1 * sinU2 - sinU1 _
        * cosU2 * cosLambda) ^ 2))
    If sinSigma = 0 Then
     distVincenty = 0 'co-incident points
      Exit Function
    End If
    cosSigma = sinU1 * sinU2 + cosU1 _
      * cosU2 * cosLambda
    sigma = Atan2(cosSigma, sinSigma)
    sinAlpha = cosU1 * cosU2 * _
      sinLambda / sinSigma
    cosSqAlpha = 1 - sinAlpha * sinAlpha

    If cosSqAlpha = 0 Then 'check for
    'a divide by zero
      cos2SigmaM = 0 '2 points on equator
    Else
      cos2SigmaM = cosSigma - 2 _
        * sinU1 * sinU2 / cosSqAlpha
    End If

    C = f / 16 * cosSqAlpha * (4 + f _
        * (4 - 3 * cosSqAlpha))
    lambdaP = lambda

'the original calculation is
'  "Too Complex" for Excel VBA
'  to deal with
'so it is broken into segments
'  to calculate without that issue
'the original implementation
'  to calculate lambda
'lambda = L + (1 - C) * f * sinAlpha * _
  (sigma + C * sinSigma * (cos2SigmaM
'  + C * cosSigma * (-1 + 2
'  * (cos2SigmaM ^ 2))))
      'calculate portions
    P1 = -1 + 2 * (cos2SigmaM ^ 2)
    P2 = (sigma + C * sinSigma * _
      (cos2SigmaM + C * cosSigma * P1))
    'complete the calculation
    lambda = L + (1 - C) * f _
      * sinAlpha * P2

  Wend

  If iterLimit > 1 Then
   MsgBox _
   "iteration limit has been reached," _
        & " something didn't work."
    Exit Function
  End If

  uSq = cosSqAlpha * (low_a ^ 2 _
    - low_b ^ 2) / (low_b ^ 2)

'the original calculation is
'  "Too Complex" for Excel VBA
'  to deal with
'so it is broken into segments to
'  calculate without that issue
  'the original implementation to
'  calculate upper_A
  'upper_A = 1 + uSq / 16384 *
'  (4096 + uSq * (-768 + uSq *
'  (320 - 175 * uSq)))
  'calculate one piece of the equation
  P1 = (4096 + uSq * (-768 _
    + uSq * (320 - 175 * uSq)))
  'complete the calculation
  upper_A = 1 + uSq / 16384 * P1

  'oddly enough, upper_B calculates
'  without any issues - JLatham
  upper_B = uSq / 1024 * (256 + uSq _
    * (-128 + uSq * (74 - 47 * uSq)))

'the original calculation is
'  "Too Complex" for Excel VBA
'  to deal with
'so it is broken into segments to
'  calculate without that issue
  'the original implementation to
'  calculate deltaSigma
  'deltaSigma = upper_B * sinSigma *
'  (cos2SigmaM + upper_B / 4 *
'  (cosSigma * (-1 + 2 
'   * cos2SigmaM ^ 2) _
     - upper_B / 6 * cos2SigmaM *
'   (-3 + 4 * sinSigma ^ 2) * 
'   (-3 + 4 *cos2SigmaM ^ 2)))
  'calculate pieces of the
'  deltaSigma formula
  'broken into 3 pieces to prevent
'  overflow error that may occur in
  'Excel 2010 64-bit version.
  P1 = (-3 + 4 * sinSigma ^ 2) * _
    (-3 + 4 * cos2SigmaM ^ 2)
  P2 = upper_B * sinSigma
  P3 = (cos2SigmaM + upper_B / 4 * _
   (cosSigma * (-1 + 2 _
      * cos2SigmaM ^ 2) - _
     upper_B / 6 * cos2SigmaM * P1))
  'complete deltaSigma calculation
  deltaSigma = P2 * P3

  'calculate the distance
  s = low_b * upper_A * _
    (sigma - deltaSigma)
  'round distance to millimeters
  distVincenty = Round(s, 3)

End Function
'======================================
Function SignIt(Degree_Dec As String) _
    As Double
'Input:  a string representation of
'  a lat or long in the
'         format of 10° 27' 36" S/N
'  or 10~ 27' 36" E/W
'OUTPUT:  signed decimal value
'  ready to convert to radians
'
  Dim decimalValue As Double
  Dim tempString As String
  tempString = UCase(Trim(Degree_Dec))
  decimalValue = _
    Convert_Decimal(tempString)
  If Right(tempString, 1) = "S" _
    Or Right(tempString, 1) = "W" Then
    decimalValue = decimalValue * -1
  End If
  SignIt = decimalValue
End Function
'======================================
Function Convert_Degree(Decimal_Deg) _
  As Variant
'source: https://support.microsoft.com/
'  kb/213449
'
'converts a decimal degree
'  representation to deg min sec
'as 10.46 returns 10° 27' 36"
'
  Dim degrees As Variant
  Dim minutes As Variant
  Dim seconds As Variant
  With Application
     'Set degree to Integer of
'  Argument Passed
     degrees = Int(Decimal_Deg)
     'Set minutes to 60 times the
'  number to the right
     'of the decimal for the
'  variable Decimal_Deg
     minutes = (Decimal_Deg - _
    degrees) * 60
     'Set seconds to 60 times the
'  number to the right of the
     'decimal for the variable Minute
     seconds = Format(((minutes - _
    Int(minutes)) * 60), "0")
     'Returns the Result of degree
'  conversion
    '(for example, 10.46 = 10° 27' 36")
     Convert_Degree = " " & degrees _
    & "° " & Int(minutes) & "' " _
         & seconds + Chr(34)
  End With
End Function
'======================================
Function Convert_Decimal _
    (Degree_Deg As String) As Double
'source: https://support.microsoft.com/
'  kb/213449
   ' Declare the variables to be
'  double precision floating-point.
   ' Converts text angular entry to
'  decimal equivalent, as:
   ' 10° 27' 36" returns 10.46
   ' alternative to ° is permitted:
'  Use ~ instead, as:
   ' 10~ 27' 36" also returns 10.46
   Dim degrees As Double
   Dim minutes As Double
   Dim seconds As Double
   '
   'modification by JLatham
   'allow the user to use the ~
'  symbol instead of ° to denote degrees
   'since ~ is available from the
'  keyboard and ° has to be entered
   'through [Alt] [0] [1] [7] [6]
'  on the number pad.
   Degree_Deg = Replace(Degree_Deg, _
    "~", "°")

   ' Set degree to value before
'  "°" of Argument Passed.
   degrees = Val(Left(Degree_Deg, _
    InStr(1, Degree_Deg, "°") - 1))
   ' Set minutes to the value between
'  the "°" and the "'"
   ' of the text string for the variable
'   Degree_Deg divided by
   ' 60. The Val function converts the
'   text string to a number.
   minutes = Val(Mid(Degree_Deg, _
    InStr(1, Degree_Deg, "°") + 2, _
      InStr(1, Degree_Deg, "'") - _
    InStr(1, Degree_Deg, "°") - 2)) / 60
   ' Set seconds to the number to the
'  right of "'" that is
   ' converted to a value and then
'  divided by 3600.
   seconds = Val(Mid(Degree_Deg, _
    InStr(1, Degree_Deg, "'") + _
      2, Len(Degree_Deg) - _
    InStr(1, Degree_Deg, "'") - 2)) _
    / 3600
   Convert_Decimal = degrees _
    + minutes + seconds
End Function
'======================================
Private Function toRad(ByVal _
    degrees As Double) As Double
    toRad = degrees * (PI / 180)
End Function
'======================================
Private Function Atan2( _
    ByVal X As Double, _
    ByVal Y As Double) As Double
 ' code nicked from:
 ' https://en.wikibooks.org/wiki/
'  Programming:Visual_Basic_Classic/
'  Simple_Arithmetic
'  #Trigonometrical_Functions
 ' If you re-use this watch out:
'  the x and y have been reversed from
'  typical use.
    If Y > 0 Then
        If X >= Y Then
            Atan2 = Atn(Y / X)
        ElseIf X <= -Y Then
            Atan2 = Atn(Y / X) + PI
        Else
        Atan2 = PI / 2 - Atn(X / Y)
    End If
        Else
            If X >= -Y Then
            Atan2 = Atn(Y / X)
        ElseIf X <= Y Then
            Atan2 = Atn(Y / X) - PI
        Else
            Atan2 = -Atn(X / Y) - PI / 2
        End If
    End If
End Function
'======================================

Copy Excel VBA Code to a Regular Module

To see the steps for pasting a macro into a workbook, and running the macro, please watch this short video tutorial. The written instructions are on the Add Code to Excel Workbook page.

Get the Workbook

To see the code, and test the formulas, download the Excel Distance Calculation sample workbook. The file is in xlsm format, and is zipped. There are macros in the workbook, so enable the macros if you want to test the code.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

Excel Function Tutorials

Last updated: June 3, 2024 10:11 AM