Newton Excel Bach, not (just) an Excel Blog

Wikipedia has detailed articles on:
Spherical Geometry  and
Vincenty’s Formulae (for geometric calculations on an ellipsoid).

The spreadsheet Vincenty.zip uses those resources to perform the following calculations, using both on-sheet calculations and VBA user-defined functions (UDF’s):

1. Calculation of distance and azimuth angles given latitude and longitude for two points.
2. Calculation of latitude and longitude and azimuth for a second point, given the position and azimuth for the first point.
3. Calculation of the area inside a closed polygon, using the excess angle method.

The spreadsheet contains full open source code, and documentation for the on-sheet calculations.  Examples are shown in the screenshots below (click any image for full-size view):

Input  and  geometry constants required for the VincentyLen  Function:

The on-sheet calculation follows the procedure given in the Wikipedia article.  The VincentyLen function returns the same end results (in bold), and can also return the intermediate results:

The  VincentyCoord function solves the “Direct Problem”, returning the latitude and longitude for a point at a specified distance and bearing from another point:

The VincentyArea Function finds the area inside a polygon specified by the latitude and longitude for a series of points, listed in clockwise order, using the “Excess angle method”. Note that as well as the returning the area, if the optional second argument is set to 2 the function returns additional data for each segment of the polygon, as shown in columns H to L below:

The VincentyArea function has been used to calculate the area of the Australian mainland, using a series of 2900 points:

The data for the points is listed with longitude first then latitude.  All three functions have an optional third “XY” argument to deal with this convention with the values:

• 1 (default) – Latitude then Longitude
• 2 – Longitude, Latitude