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))
___
Addendum 2: and here’s a google sheet you can use (and download to excel). And here is the youtube video explaining!
Not Unreasonable 
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!
How much is the accuracy for haversine formula?
Thank you! Finally figured out thanks to your post that atan2 variables are switched in Excel and Google Sheets!