Excel - As-the-crow-flies distance function
The demo file offers three ways to calculate the
as-the-crow-flies distance, the shortest distance between two geographical locations specified by latitude and longitude
- Regular formula. It is a pretty long one, that might be difficult to link to its inputs and/or copy across.
- VBA - User Defined Function (UDF) GlobeDistance. Much easier to read and link. But will execute slower. You can set its optional argument InMiles to TRUE/FALSE to retrieve distance in miles/kilometers. Default is in kilomters!
- LAMBDA function L_GlobeDistance. LAMBDA functionality is new in Office365/Excel 2021. It enables you to directly create a function within Name Manager. Works the same as the VBA UDF GlobeDistance. You can set its optional argument InMiles to TRUE/FALSE to retrieve distance in miles/kilometers (if omitted, then it returns the distance in kilometers, as its default!).
Download demo file (32kB - downloaded
1974 times)
Regular formula
Given the following input fields (in degrees): From latitude (cell A4), From longitude (cell B4), To latitude (cell C4), To longitude (cell D4), InMiles (cell E4) the formula is
Dependent on your MS Excel setting you may have to use comma or semicolon in your formula.
User Defined Function GlobeDistance: VBA code to be put in a module
In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the MS Excel VBA code below into the module.
Note: in general, a user defined function will execute slower than a standard function, which can become a disadvantage in case of a large amount of formulas.
LAMBDA Function L_GlobeDistance
