Interactive Spreadsheets for Celestial Navigation

Noon shots

1. Noon sight

Observing the sun at LAN (local apparent noon) allows you to determine the position from a single sight. The need for a second line of position is eliminated by the additional piece of information implicitly contained in a noon sight: i.e. that the sun and the observer are on the same meridian. Thus the geometric arrangement is reduced to a 1-dimensional problem along this meridian, with the sun bearing either directly north or south from the observer. In the spreadsheet noon_sight.xls the Ho at LAN is entered in cell D1 and the UT in cell D13. From an almanac you find the sun’s declination (D3) and the correction for equation of time; the latter is entered in D14 if positive, or in D15 if negative (without its sign). This correction is always less than one hour, therefore it is to be entered as 00:MM:SS. It can be (optionally) copied from cell B16 that interpolates between cells B14, B15; therein you may enter the equation of time values (without their signs) for the 00^h and 12^h instants that straddle your noon UT in cell D13. (This interpolator works in the vast majority of cases when the two equation of time values are of the same sign.) The final two entries identify the observer’s hemisphere (D4) and the sun’s bearing (D5); enter N for north and S for south. In the special case of the sun in zenith (Ho = F1 = 90°) your position is the sun’s GP and the sun’s bearing (D5) is as unimportant as it is undefined. The latitude of the position is displayed in cells D9, D10, D11 and cells D16, D17, D18 contain the longitude.

The following image shows the spreadsheet noon_sight.xls.

Summary for spreadsheet noon_sight.xls:

Input cells: D1, D3, D4, D5, D13, D14, D15,
(B14, B15 optional)
Output cells: D9, D10, D11, D16, D17, D18
Intermediate cell: B16

2. Noon curve

The noon sight in principle allows you to determine the position and indeed the latitude can be measured to a good accuracy. However, in practice the inferred longitude is often inaccurate due to the difficulty of marking the precise moment of LAN. The sun hangs at its maximum altitude for a couple of minutes and every four seconds of uncertainty in the time of LAN introduce an error of 1 arc minute of longitude.

In order to mitigate this problem with the noon sight it is recommended to make several observations around the time of LAN, fit the measurements with a “noon curve” and infer the Ho and UT from this fit. The spreadsheet noon_curve.xls does precisely that. It is an extended version of the noon_sight.xls spreadsheet with the difference that the Ho (H1) and UT (H13) at LAN are computed from the noon curve instead of being entered by the user.

The noon curve is constructed via the following steps:

Enter the UT’s of your sights in column A and the corresponding Ho’s in column B. You will need at least three observations for the noon curve (which is a quadratic fit) to be defined.

Insert a Chart (XY scatter type) with column B as the Y axis (“Data range” tab) and column A for X-values (“Series” tab).

Right-click on the plotted curve and select “Add Trendline” from the context menu. In the “Type” tab select “polynomial” of order 2; in the “Options” tab check “Display equation on chart.”

Find the noon curve fitting equation of the type y = ax² + bx + c on the plot, retrieve the a, b, c coefficients (complete with signs) and enter them into cells F1, F2, and F3.

The entries for Hemisphere (F4), Sun bearing (F5), and Equation of time (H14 or H15, plus the optional interpolation data in cells F14, F15) are entered as in the noon_sight.xls spreadsheet.

The position is displayed in cells H9, H10, H11 (latitude) and in cells H16, H17, H18 (longitude).

The following image shows the spreadsheet noon_curve.xls.

Summary for spreadsheet noon_curve.xls:

Input cells: column A, column B, F1, F2, F3, H3, H4, H5, H14, H15,
(F14, F15 optional)
Output cells: H9, H10, H11, H16, H17, H18
Intermediate cell: F16

Alternatively, you may also use the spreadsheet noon_motion.xls (see below), which produces the same results without the need for plotting a chart. That spreadsheet computes the coefficients a, b, and c of the quadratic fit automatically. Extra pieces of information on input include the number of observations in cell F4. Also, in this context set F1 cell value to zero and enter a solstice day (June or December 21) in cells F6 and F7.

3. Noon curve on a moving vessel

The movement of the observer and Sun’s declination change both distort the idealized parabolic noon-curve shape treated by the noon_curve.xls spreadsheet. The spreadsheet noon_motion.xls accounts for both of these effects assuming a uniform speed (F1) and course (F2) of the vessel for the duration of the procedure. The number of observations goes to cell F4 and the date is entered in cells F6 and F7. All other cells have the same meaning as in the noon_curve.xls spreadsheet with important clarifications. The coordinates of the fix (H9-11 and H16-18) pertain to the vessel’s position at the moment of the last observation; it is this moment for which the declination of the Sun (H3) should be entered. The equation-of-time value (H14 or H15), however, applies to the computed instant of local apparent noon (H13).

The following image shows the spreadsheet noon_motion.xls.

Summary for spreadsheet noon_motion.xls:

Input cells: column A, column B, F1, F2, F4, F6, F7, H3, H4, H5, H14, H15, (F14, F15 optional)
Output cells: H9, H10, H11, H16, H17, H18
Intermediate cells: F16, H13

4. Meridian transit on a moving vessel

The spreadsheet transit.xls is a generalization of noon_motion.xls that can process meridian transit data (both upper and lower) for any celestial body. The rate of change of declination (in arc minutes per hour) is entered in cell F7. In cell H15 enter “U” for upper and “L” for lower meridian transit.

The following image shows the spreadsheet transit.xls.

Summary for spreadsheet transit.xls:

Input cells: column A, column B, F1, F2, F4, F7, H3, H4, H5, H14, H15
Output cells: H9, H10, H11, H16, H17, H18
Intermediate cell: H13

5. Ex-meridian latitude calculation

The ex_meridian.xls spreadsheet has the same general format of input and output cells as the other meridian-transit-category spreadsheets. The one extra input data point is in cell B11 marking the time away from the actual meridian transit in the hours:minutes:seconds (HH:MM:SS) format. Cell B12 displays the intermediate results: (altitude factor) from Bowditch Table 24, and cell B13 shows the absolute value of the resulting change in altitude/latitude from Bowditch Table 25.

The ex_meridian_t.xls version of this spreadsheet takes the meridian angle t (in degrees) as input in cell B11.

The following image shows the spreadsheet ex_meridian.xls.

Summary for spreadsheets ex_meridian.xls, ex_meridian_t.xls:
Input cells: B1, B3, B4, B5, B9, B11, B15
Output cells: B16, B17, B18
Intermediate cells: B12, B13

DOWNLOAD TAR ARCHIVE FILE (13 MB)

DOWNLOAD ZIP ARCHIVE FILE (7 MB)