Interactive Spreadsheets for Celestial Navigation
Lines of position fix

1. Lines of position (two-body) fix

If, for instance, both the sun and the moon are simultaneously visible in the sky it is possible to obtain your position by finding the intersection of the two lines of position  (LOP) obtained from each sight.  Typically there are two distinct possible positions and it should be very easy to decide which one is the correct one.

The measured sextant altitude is corrected for index error, refraction, parallax, and semidiameter which results in the observed altitude (Ho).  The Universal Time (UT) of the sight is used to determine the Geographical Position (GP) with the help of an almanac spreadsheet. The declination and Greenwich Hour Angle (GHA) of the GP plus the Ho  for the two sights are entered in row 3 of the spreadsheet.  The solutions are displayed in rows 8 and 10.

The following image shows the spreadsheet lops.xls.

Summary for spreadsheet lops.xls:

Input cells: A3, B3, C3, D3, E3, F3
Output cells: A8, B8, C8, D8, E8, F8, A10, B10, C10, D10, E10, F10

The problem preset in this spreadsheet is treated in The Celestial Navigation Mystery Solved by David Owen Bell on p. 79.

The spreadsheet two_body_fix.xls has the same interface as lops.xls and solves the same problem using a different method.  Whereas lops.xls employs the techniques of spatial geometry (Van Allen paper), two_body_fix.xls applies the equations of spherical trigonometry (John Karl, Celestial Navigation in the GPS Age, pp. 78-79).  The latter reference also discusses the applicability of this approach to finding the latitude without a meridian sight or knowledge of UT (the double-altitude method).

2. Lines of position (many-body) fix

Spreadsheet many_body_fix.xls implements a procedure for computing a location from multiple lines of position obtained on a moving vessel.  This navigation problem typically arises from a round of observations acquired during twilight.  Enter the desired UT of the fix in cell A2, vessel speed in knots in cell B2, and course (track) in cell C2.  The initial best guess for the position goes into cells E2 and F2.  The observational and GP data start in line 9 and are entered into columns A (name), B (UT of observation), C (observed altitude Ho), E (GHA), and G (declination).  The computed coordinates are displayed in row 6.  The spreadsheet performs a block of four iterations of this procedure.  On output, the value d in cell D4 should be less than 20 nautical miles; if that is not the case, copy cells A6 and D6 into cells E2 and F2 and repeat this procedure until convergence is reached.

The following image shows the spreadsheet many_body_fix.xls.

Input cells: A2, B2, C3, E2, F2, from row 9 down columns A, B, C, E, G
Output cells: D4, B6, C6, E6, F6

The method and the problem preset in this spreadsheet were taken from pp. 282-283 of the Nautical Almanac, 2010 Commercial Edition.



Web Hosting Companies