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

### Like this:

Like Loading...

*Related*

Nice, thanks for pointing out the Excel reversal.

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.

Will the distance calculated be in Miles/Km ??

Brilliant. Thanks for the ATAN2 tip.

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

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!

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!

Many thanks, very very useful!!!

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

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

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

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

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

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

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?

Many, Many, Many Thanks for the ATAN2 tip!