Thaddeus Vincenty's Direct and Inverse formulae for geodesic calculations in Excel (distance, azimuth, latitude, longitude).
to calculate new coordinate based on azimuth and distance (direct)
or distance and azimuth based on two coordinates (inverse)
Algorithms by Thaddeus Vincenty (1975)
Based on the implementation in Java Script by Chris Veness
https://www.movable-type.co.uk/scripts/latlong-vincenty.html
https://github.com/chrisveness/geodesy
To make the long story short, I was looking for a way to calculate coordinates, distance and azimuth in Excel. I checked out several available solutions but they were either incomplete, did not work or results were inaccurate. That is how I ended up developing my own, complete Vincenty's Direct and Inverse formulae implementation.
=Vincenty..
and you should see the list of added functions. Do NOT simply copy-paste file text content to a new Excel module - file contains some extra lines not visible in VBA editor.Solution contains 6 functions implementing Vincenty's Direct and Vincenty's Inverse formulae as well as 2 functions for Decimal ↔ Degrees/Minutes/Seconds format conversion, and uses WGS84 model.
Most function arguments and return values are 64-bit high precision. In VBA
Double
data type denotes 64-bit floating-point number, regardless of Excel edition (32/64 bit).
VincentyDirLat(lat as Double, lon as Double, azimuth as Double, distance as Double) as Variant
Calculates geodesic latitude (in degrees) based on one point, bearing (in degrees) and distance (in m) using Vincenty's direct formula for ellipsoids.VincentyDirLon(lat as Double, lon as Double, azimuth as Double, distance as Double) as Variant
Calculates geodesic longitude (in degrees) based on one point, bearing (in degrees) and distance (in m) using Vincenty's direct formula for ellipsoids.VincentyDirRevAzimuth(lat as Double, lon as Double, azimuth as Double, distance as Double, [returnAzimuth as Boolean = False]) as Variant
Calculates geodesic reverse azimuth (in degrees) based on one point, bearing (in degrees) and distance (in m) using Vincenty's direct formula for ellipsoids.
Note: by default azimuth from point 1 to point 2 at point 2 is returned. To obtain azimuth from point 2 to point 1 pass returnAzimuth = true
.VincentyInvDistance(lat1 as Double, lon1 as Double, lat2 as Double, lon2 as Double) as Variant
Calculates geodesic distance (in m) between two points specified by latitude/longitude (in numeric degrees) using Vincenty's inverse formula for ellipsoids.VincentyInvFwdAzimuth(lat1 as Double, lon1 as Double, lat2 as Double, lon2 as Double) as Variant
Calculates geodesic azimuth (in degrees) between two points specified by latitude/longitude (in numeric degrees) using Vincenty's inverse formula for ellipsoids.VincentyInvRevAzimuth(lat1 as Double, lon1 as Double, lat2 as Double, lon2 as Double, [returnAzimuth as Boolean = False]) as Variant
Calculates geodesic reverse azimuth (in degrees) between two points specified by latitude/longitude (in numeric degrees) using Vincenty's inverse formula for ellipsoids.
Note: by default azimuth from point 1 to point 2 at point 2 is returned. To obtain azimuth from point 2 to point 1 pass returnAzimuth = true
.ConvertDegrees(decimalDeg as Double, optional isLongitude as Variant) as String
Converts decimal latitude, longitude or azimuth value to degrees/minutes/seconds string format. If isLongitude value is provided output will be formatted as either longitude (true) or latitude (false).ConvertDecimal(degreeDeg as String) as Variant
Converts latitude, longitude or azimuth string in degrees/minutes/seconds format to decimal value. This function has been designed to parse typical formats.NormalizeLat(lat as Double) as Double
Normalizes latitude to -90..+90 range.NormalizeLon(lon as Double) as Double
Normalizes longitude to -180..+180 range.NormalizeAzimuth(azimuth as Double, [positiveOnly as Boolean = False]) as Double
Normalizes azimuth to 0..360 range. Note: by default input and return values have the same sign. To obtain only positive values pass positiveOnly = true
.From2000Lat(x As Double, y As Double, meridian As Integer) As Double
From2000Lon(x As Double, y As Double, meridian As Integer) As Double
To2000X(lat As Double, lon As Double, meridian As Integer) As Double
To2000Y(lat As Double, lon As Double, meridian As Integer) As Double
Excel workbooks contain unprotected source code. In addition, for better change tracking, source code has been placed separately in Vincenty.bas file. This file is all what is required to add implemented functions to any other Excel workbook.
Calculation results have been validated using 1200 test cases generated for 6 range clusters and distance between 10 m and 30,000 km against GeographicLib by Charles Karney:
and Geoscience Australia website
GeographicLib | Geoscience Australia | |
---|---|---|
VincentyDirLat [degrees] | 1.11E-9 | 3.96E-8 |
VincentyDirLon [degees] | 6.54E-9 | 2.88E-7 |
VincentyDirRevAzimuth [degrees] | 6.54E-9 | 5.05E-7 |
VincentyInvDistance [mm] | 0.07240 | 0.53655 |
VincentyInvFwdAzimuth [degrees] | 1.46E-6 | 1.46E-6 |
VincentyInvRevAzimuth [degrees] | 1.46E-6 | 1.47E-6 |
I was only able to compare the achieved results with Geoscience Australia and GeographicLib, which is believed to be very accurate. I am not aware of substantially better references available publicly.
For complete test results refer to VincentyTest.xlsm file.
Finally, if you find this tool useful please give it a star. This way others will be able to find it more easily. Do not hesitate leave comments/suggestions.