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))

Advertisements
This entry was posted in programming. Bookmark the permalink.

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

  1. track says:

    Nice, thanks for pointing out the Excel reversal.

  2. Thanks, very useful.

    NB. Quick note for others: I copy and pasted the full formula. I had to change the subtraction symbols, as these were not recognised by Excel.

  3. Amit says:

    Will the distance calculated be in Miles/Km ??

  4. Stuart says:

    Brilliant. Thanks for the ATAN2 tip.

  5. Very useful, and if you would like the result in miles you can change 6371 to 3958.756

  6. John says:

    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!

  7. wptohr says:

    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!

  8. Wagner Filho says:

    Many thanks, very very useful!!!

  9. Les Pedersen says:

    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

  10. Craig S says:

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

  11. cyrilbrd says:

    @Les Pedersen, do you mean such as this: =DHalverson(lat_1,long_1,lat_2,long_2,”km”) ?

  12. cyrilbrd says:

    @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.

  13. 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)))))

  14. charleslparker says:

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

  15. Paul says:

    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?

  16. Silvio Reis says:

    Many, Many, Many Thanks for the ATAN2 tip!

  17. Manoj Pai says:

    How much is the accuracy for haversine formula?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s