Lat/Lon Distance Excel (Spherical Law of Cosines and Haversine Formula in Excel)

I can’t find this in one place anywhere, so I’m putting it up here:

Here’s the Haversine Formula in excel. Make sure the lat and lons are in radians (multiply degrees by pi/180)

cellx=SIN((lat2 – lat1)/2)^2+COS(lat1)*COS(lat2)*SIN((lon2-lon1)/2)^2

celly=2*Atan2(sqrt(1-cellx),sqrt(cellx)) // big trick here is that ATAN2 is reversed in Excel relative to most programming languages. Beware!

answer=celly * 6371

And the spherical law of cosines (same deal for radians. Don’t use degrees!):

=ACOS(SIN(LAT1)*SIN(LAT2)+COS(LAT1)*COS(LAT2)*COS(LON2-LON1))*6371

I’ve found the the spherical law of cosines does the trick for just about anything and is nice to have in one formula. Not that you couldn’t do the Haversine in one line, but it would be tough to look at.

___

Addendum: here’s the full excel Haversine formula in one line:

distance = 6371*2*Atan2(sqrt(1-(SIN((lat2-lat1)/2)^2+COS(lat1)*COS(lat2)*SIN((lon2-lon1)/2)^2)),sqrt(SIN((lat2-lat1)/2)^2+COS(lat1)*COS(lat2)*SIN((lon2-lon1)/2)^2))

18 thoughts on “Lat/Lon Distance Excel (Spherical Law of Cosines and Haversine Formula in Excel)

  1. Thanks for the note on ATAN2 being reversed in excel – I was racking my brain trying to figure out why my answer was so far off!

  2. Ditto on the ATAN2 in Excel…my answer came back as nearly PI…which was quite mysterious. Then I found your warning that Excel reversed the ATAN2 function – problem solved. THANK YOU!

  3. Thanks much! I’ll use this often so I wrote a VBA User Defined Function incorporating the formula, and added functionality to accept input in decimal degrees rather than radians and converts the result to your unit of measure of choice.

    I modified the formula so that the default result is in meters, but added a “Unit” argument. Using the optional “Unit” argument, the function will return results in the unit of measure so indicated. The unit argument is limited to values valid for Excel’s “Convert()” function as that function is used within the VBA code.

    Also, this function uses decimal degrees input rather than radians. Conversion to radians is performed within the function.

    Copy and paste into a module of any macro-enabled Excel file. Add it to your personal.xls? file to be available in all workbooks. The code:

    Function DHalverson(Lat_1 As Double, Lon_1 As Double, Lat_2 As Double, Lon_2 As Double, Optional Unit As String) As Double
    ‘Calculates the distance between two sets of geodetic coordinates using the Halverson Formula. Coordinates must be in in decimal degrees. Default results are in meters.
    ‘Leslie Pedersen, December 2014
    Dim dHalv As Double
    Lat_1 = Lat_1 * PI / 180
    Lat_2 = Lat_2 * PI / 180
    Lon_1 = Lon_1 * PI / 180
    Lon_2 = Lon_2 * PI / 180

    dHalv = 6371000 * 2 * Application.WorksheetFunction.Atan2(Sqr(1 – Sin((Lat_2 – Lat_1) / 2) ^ 2 + Cos(Lat_1) * Cos(Lat_2) * Sin((Lon_2 – Lon_1) / 2) ^ 2), Sqr(Sin((Lat_2 – Lat_1) / 2) ^ 2 + Cos(Lat_1) * Cos(Lat_2) * Sin((Lon_2 – Lon_1) / 2) ^ 2))
    If Unit = “” Then Unit = “m”
    Select Case Unit
    Case “m” ‘Meter
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “m”)
    Case “km” ‘Kilometer
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “km”)
    Case “cm” ‘Centimeter
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “cm”)
    Case “mm” ‘Millimeter
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “mm”)
    Case “mi” ‘Statutemile
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “mi”)
    Case “Nmi” ‘Nauticalmile
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “Nmi”)
    Case “in” ‘Inch
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “in”)
    Case “ft” ‘Foot
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “ft”)
    Case “yd” ‘Yard
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “yd”)
    Case “ang” ‘Angstrom
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “ang”)
    Case “ell” ‘Ell
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “ell”)
    Case “ly” ‘Light-year
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “ly”)
    Case “parsec” ‘Parsec
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “parsec”)
    Case “Picapt” ‘Pica(1/72inch)
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “picapt”)
    Case “pica” ‘Pica(1/6inch)
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “pica”)
    Case “survey_mi” ‘U.S survey mile
    DHalverson = Application.WorksheetFunction.Convert(dHalv, “m”, “survey_mi”)
    Case Else
    MsgBox “You have entered an invalid unit. Unit entered must be enclosed with quotation marks and must be a distance unit recognized by Excel’s Convert() function. Valid unit abbreviations are m, km, cm, mm, mi, Nmi, in, ft, yd, ang, ell, ly, parsec, Picapt, pica, and survey_mi.”
    End Select
    End Function

  4. I really can’t get this to work. Can anyone upload a spreadsheet or are willing to email me one?

  5. @Craig S
    Given a metric environment with latitude and longitudes in degree
    Haversine formula is: =RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(lat_1)-RADIANS(lat_2))/2)^2)+COS(RADIANS(lat_1))*COS(RADIANS(lat_2))*(SIN((RADIANS(long_1)-RADIANS(long_2))/2)^2)))))

    RadiusEarth is 6371 km
    Formula returns distance in kilometers.

  6. Beware one thing in Excel. If you have latitute & longitude columns in dd:mm:ss [actually hh:mm:ss because Excel doesn’t have dd:mm:ss as a formatting option], not only must you convert them to radians; you must also multiply all the angles by 24! This is because they are treated as fractions of a 24 hour day rather than a proportion of a 360 degree circle.

    This is well explained at http://www.cpearson.com/excel/LatLong.aspx – which is also the only place I found with a formula which gets *everything* right:

    =RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(Lat1_*24)-RADIANS(Lat2_*24))/2)^2)+COS(RADIANS(Lat1_*24))*COS(RADIANS(Lat2_*24))*(SIN((RADIANS(Long1_*24)-RADIANS(Long2_*24))/2)^2)))))

  7. Six years later, it’s still useful to know that the parameters of atan2 are reversed in Excel (also on Google Sheets)

  8. This is a great formula! I was wondering (since my algebra is terrible) how could this formula be rewritten to find say exactly 1 Nautical mile from about 50 different points. Could this be done using excel?

  9. Thank you! Finally figured out thanks to your post that atan2 variables are switched in Excel and Google Sheets!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.