Interactive Spreadsheets for Celestial Navigation

Solutions of the navigation triangle

1. Sight reduction using the intercept method

The spreadsheet intercept.xls solves the navigation triangle in accordance with the intercept method of Marcq Saint-Hilaire. Enter the latitude and longitude of the assumed position (AP) in cells A2, B2, the GHA and Declination (GP) in cells C2, D2, and the observed altitude (Ho) in cell E2. Cell F2 computes and displays the Local Hour Angle (LHA). If you have already determined LHA and want to use it as input, enter it in place of GHA (cell C2) and set the AP Longitude (in cell B2) to zero. The calculated altitude (Hc) at the AP is in cells A6, B6, C6 and the intercept distance (in nautical miles) is displayed in cells D6 and E6. The azimuth Zn toward the GP from the assumed position is in cell F6 This allows you to plot the line of position (LOP), along which the “true” position (TP) is located.

Rows 7, 9, 12, and 15 contain additional information about LOP properties that help the plotting of the LOP without the azimuth line using the T-Plotter™. This reduces the clutter on the chart when multiple LOP's are plotted.

The following image shows the spreadsheet intercept.xls.

Summary for spreadsheet intercept.xls:
Input cells: A2, B2, C2, D2, E2
Output cells: A6-F6, D7-F7, D9, E9, C12-F12, C15-F15
Intermediate cell: F2

It is also possible to use this spreadsheet to precompute altitudes before an observation. For that purpose the computed altitude Hc displayed in cell A6 can be further matched to the actual observation conditions with spreadsheet alt_prec.xls (enter Hc in cell B12), which corrects for refraction, semidiameter, parallax, and index error.

The calculated LOP on a plotting sheet:

In order to see another worked-out example please visit our blog.

2. The one-body fix

If both the azimuth (Zn) and the altitude (Ho) of a celestial body is known, then it is possible to obtain a fix for the “true” position (TP) from observing that one body alone. The spreadsheet one_body_fix.xls solves the navigation triangle in this scenario. The GP is characterized by its declination (cell A2) and Greenwich Hour Angle (B2). The azimuth (Zn) is entered in cell C2, and the observed altitude (Ho) goes into cell D2. The TP coordinates are displayed in row 6.

The following image shows the spreadsheet one_body_fix.xls.

Summary for spreadsheet one_body_fix.xls:
Input cells: A2, B2, C2, D2
Output cells: A6, B6, C6, D6, E6, F6

The data preset in this spreadsheet define an inverse problem to the one preset in intercept.xls.

3. Great-circle and rhumb-line sailings

This group of spreadsheets (sailings.xls, waypoints.xls, composite.xls) can assist you in planning your trip from the point of Departure to Destination. The example preset in these spreadsheets pertains to a trip from San Francisco (USA) to Yokohama (Japan). This choice is inspired by Figure 2404 in Bowditch (p. 347).

In spreadsheet sailings.xls you enter the coordinates of the Departure and Destination points in row 2. The spreadsheet calculates the distances and courses from Departure to Destination along the resulting great circle (columns B, C) and rhumb line (columns E, F). The results are displayed both assuming a perfectly spherical (row 6) as well as a flattened (row 7) Earth model. The initial great-circle course in the yellow cell C6 is displayed with (otherwise unrealistic) three decimal places in order to minimize numerical round-off errors when that value is subsequently copied into the D2 input cell of the spreadsheet waypoints.xls. Finally, row 11 displays the coordinates of the great-circle vertex on the path from Departure toward Destination.

The following image shows the spreadsheet sailings.xls.

Summary for spreadsheet sailings.xls:
Input cells: B2, C2, E2, F2
Output cells: B6, C6, E6, F6, B7, C7, E7, F7, row 11

The spreadsheet waypoints.xls takes the coordinates of the Departure point (cells B2, C2) and (in cell D2) the initial course (e.g. from cell C6 in sailings.xls). Row 7 displays the great-circle vertex. Starting in row 11 you can use column A to specify the longitude of each waypoint along the great circle. The spreadsheet then calculates the corresponding latitude (columns C, D, E) and the (flattened Earth model) rhumb-line distance (column F) and the constant course (column G) from the previous waypoint. The total length of this path is shown in cell F2.

The following image shows the spreadsheet waypoints.xls.

Summary for spreadsheet waypoints.xls:
Input cells: B2, C2, D2, column A starting in row 11
Output cells: F2, row 7, columns C, D, E, F, G starting in row 11

For more information on the preset example please visit the SAILINGS entry on our blog.

4. Composite sailing

The spreadsheet composite.xls takes the initial great-circle course from sailings.xls (cell C6) and modifies this calculated great-circle route from the point of Departure (cells B2, C2) to Destination (cells E2, F2) so as not to go beyond the chosen limiting parallel of latitude (cell B9). The resulting two waypoints along this limiting parallel (plus the third waypoint, which is the Destination) are displayed in rows 12, 13, and 14. The courses shown in cells F12 and F14 are the initial great-circle (spherical Earth) courses to the first waypoint and to the Destination, respectively. The constant course in cell F13 reflects the east or west direction of the latitude sailing along the limiting parallel from the first to the second waypoint. If needed, waypoints along the two great-circle legs of this trip can be calculated with waypoints.xls.

Yellow cells in rows 5 and 9 display text messages about the result and status of the composite sailing calculation. These messages are:

cell D9: INVALID: the limiting parallel was chosen between the equator and either the Departure or the Destination point. Composite sailing path cannot be computed in this case. Rows 12, 13, and 14 are zeroed out.

cell F9: Composite OK: Non-trivial composite sailing path is successfully calculated.

cell E9: Unconstrained gc (great circle): The limiting parallel is between the vertex and its Pole and therefore does not affect the originally computed great-circle sailing path. The first and second waypoints are identical as they coincide with the vertex.

cell C5: Vertex beyond destination: This happens for relatively close Departure and Destination points, which are not separated by the vertex. The choice of limiting parallel is voided in this case and rows 13 and 14 are zeroed out. A great-circle sailing calculation is displayed in row 12. The Unconstrained gc message is also displayed in cell E9 in this case.

The following image shows the spreadsheet composite.xls.

Summary for spreadsheet composite.xls:
Input cells: B2, C2, E2, F2, B9
Output cells: row 7, block B12 through F14, E15

5. Amplitude

Rising and setting celestial bodies can be used to check for compass errors. In spreadsheet amplitude.xls enter whether the body is rising or setting in cell B1, the ship's latitude goes into cell B2, and cell B3 takes the declination of the body observed on the celestial horizon. The calculated amplitude in cell B4 inherits its sign from the declination input in B3. The calculated azimuth from cell B5 can be compared with the one observed and thus used to determined the error of that compass.

The following image shows the spreadsheet amplitude.xls.

Summary for spreadsheet amplitude.xls:
Input cells: B1, B2, B3
Output cells: B4, B5

DOWNLOAD TAR ARCHIVE FILE (13 MB)

DOWNLOAD ZIP ARCHIVE FILE (7 MB)