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.

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**.

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.

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

**Summary for spreadsheet one_body_fix.xls:
Input cells:**

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:**

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:**

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

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:**

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:**

DOWNLOAD TAR ARCHIVE FILE (13 MB)

DOWNLOAD ZIP ARCHIVE FILE (7 MB)