Interactive Spreadsheets for Celestial Navigation
Introduction
Lines of position fix
T-Plotter
Noon shots
Polaris
Dead reckoning
Altitude corrections
Navigation triangles
Almanac data
Lunar distance
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.
There are certain practical limitations in using quadratic fits to meridian transits. It is recommended to make at least 12-15 (preferably more) altitude observations. The measurements should straddle the instant of transit across a time interval of appropriate (peak altitude dependent) duration that is as symmetric as possible. Also, caution is in order when making and processing high-altitude (above 70º) observations.
We thank Antoine M. "Kermit" Couëtte for drawing these conclusions, which he detailed
here in this NavList posting
.
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)