Further to comments by Georg under: The angle between two vectors, in 2D or 3D I have added four alternative ways of calculating the angle between two 3D vectors to the VectorFunc spreadsheet.  The new version can be downloaded from: http://interactiveds.com.au/software/VectorFunc.xlsb.

New Ang() Function

The alternative calculations are:

• CalcType = 0: Ang = ATan(|(axb)|/ (a.b) ) (default)
• CalcType = 1: Ang = ACos((a.b)/(|a||b|))
• CalcType = 2: Ang = ASin(|(axb)|/(|a||b|))
• CalcType = 3: Ang = 2·arctan( || p/||p|| – b/||b|| || / || p/||p|| + b/||b|| ||)

Derivation of CalcType3 is given at http://www.cs.berkeley.edu/~wkahan/MathH110/Cross.pdf  (page 15)

Note that CalcType 1 is not accurate for small angles, and CalcType 2 is not accurate for angles close to 90 or 270 degrees

As an illustration of the use of the different options I have calculated the radius of a certain large spherical object, based on precise measurements of the offset of a line tangential to the surface:

Ang Function Calculations of the Radius of the Earth

In this case Methods 0, 2 and 3 have all given an exact result, but Method 1 (using the Cosine of a very small angle) has an error of 7.47 km.

To compare the results of Method 1 with the same procedure using higher precision arithmetic I have installed the latest XNumbers package (6.05.5M) which can be downloaded from http://www.thetropicalevents.com/Xnumbers60/.  This package is based on the original XNumbers, but has been modified for Excel 2007 and later and improved performance.

It can be seen that even using the Cosine the additional precision has given a near exact result.

Calculation Type 1 Using XNumbers multi-precision functions

In the download version of the spreadsheet I have converted the XNumbers functions to text for the benefit of those who do not have XNumbers installed.  To make the formulas active just press F2 to edit, and delete the ‘ at the start of the line.

XNumbers Functions