The spreadsheet alt_corr.xls performs the corrections to the sextant altitude Hs (cell B1) that are needed to produce the apparent altitude Ha (cells B6, B7, B8) and the observed altitude Ho (cells B12, B13, B14). Cell B4 contains a yes/no (Y/N) answer to the question whether a reflecting artificial horizon was used. The index correction goes to cell B2. The semidiameter correction is entered in cell B9; this is positive for lower limb and negative for upper limb observations. The height of eye in cell E1 (enter “ft” for feet or “m” for meters in cell F1) determines the dip correction. Cells E2, E3, and F3 control the refraction correction (in cell F3 state “C” for Celsius, or “F” for temperature in Fahrenheit); the standard values are Temperature = 10 ºC and Pressure = 1010 mb. Cell E6 contains the value of the horizontal parallax (HP) in arc minutes. The Moon parallax can also be corrected for the oblateness of the Earth by entering the latitude (E8) and azimuth (E9). The semidiameter value from either cell E11 (Sun - typical preset value, or from the almanac) or E12 (Moon - computed from the HP) is to be copied (with the appropriate sign characterizing the limb) into cell B11.
The following image shows the spreadsheet alt_corr.xls.
Summary for spreadsheet alt_corr.xls:
Input cells: B1, B2, B4, B11, E1, F1, E2, E3, F3, E6, (E8, E9 optional)
Output cells: B6, B7, B8, B12, B13, B14
Input/Output cells: E11, E12
The preset example contained in the spreadsheet is the upper limb Moon sight from p. 281 of the Nautical Almanac, 2009 Commercial Edition.
2. Precomputed sextant altitude
The spreadsheet alt_prec.xls is a reversed version of alt_corr.xls. It provides the altitude Hs to which the sextant may be preset before an observation. The observed altitude Ho (computed with intercept.xls) is now input in cell B12 and the sextant altitude is displayed in cells B1 and C1. The remaining cells have the same meaning as in alt_corr.xls.
The following image shows the spreadsheet alt_prec.xls.
Summary for spreadsheet alt_prec.xls:
Input cells: B2, B4, B11, B12, E1, F1, E2, E3, (E6, E8, E9 optional)
Output cells: B1, C1, B6, B7, B8
Input/Output cells: E11, E12
3. Averaging sights: 1) precomputed slope
Random errors can affect every individual sight. This problem can be mitigated by taking a set of measurements and averaging them. The spreadsheet average1.xls can perform this function for sextant altitude data (Hs). Enter the UT set in column A and the corresponding Hs set (in degrees) in column B. In cells F1 and F2 enter the expected sextant altitudes based on your position (dr.xls, almanac, intercept.xls, and alt_prec.xls spreadsheets are relevant here). This spreadsheet then calculates a weighted least-squares straight-line fit to the data, whose slope is derived from values in cells F2 and F3. From this fit it then extracts the average UT (cell G5) and Hs (cells G6, G7, G8). You also have the option of evaluating the average Hs (cells F6, F7, F8) at the UT of your choice (cell F5). Column D contains the weights (maximum=1.000) with which each particular data point is influencing the final result. The “Scatter” parameter (cell F13, in arcminutes) should be adjusted so that cell F14 is as close to 1 as possible and the weights in column D end up neither all 1.000, nor all (but one) much smaller than 1.000. Cells F10, F11, F12 should be small as they indicate the convergence of the encoded iterative procedure and the closeness of the fit to the original data. (Further details about the technique and the meaning of these cells are available upon request.) The time interval over which the average is computed should be short (about 5 minutes maximum), so that the assumed straight-line approximation remains justified. The resulting average altitude is a sextant altitude Hs and therefore should be processed with alt_corr.xls to yield the observed altitude Ho.
The following image shows the spreadsheet average1.xls.
Summary for spreadsheet average1.xls:
Input cells: column A, column B, F1, F2, F5, F13
Output cells: column D, F6, F7, F8, F10, F11, F12, F14, G5, G6, G7, G8
4. Averaging sights: 2) fitted slope
The spreadsheet average2.xls performs the same function as average1.xls, but for observed altitude data (Ho) in column B, while allowing the procedure to also choose the slope of the fit. Additional input data include the speed (cell F1) and course (cell F2) of the vessel, the hourly declination change rate (cell F5, in arcminutes), and azimuth (cell F7, in degrees) of the observed body. The remaining cells serve the same function as in average1.xls. The weights in column D should come out neither all 1.000, nor all (but two) very small.
The following image shows the spreadsheet average2.xls.
Summary for spreadsheet average2.xls:
Input cells: column A, column B, F1, F2, F5, F7, F9, F17
Output cells: column D, F10, F11, F12, F14, F15, F16, F18, G9, G10, G11, G12
The spreadsheet dip_short.xls implements the formula behind Table 14 in Bowditch. The height of eye can be entered in meters or feet (enter "m" or "ft" in cell C1). The distance to the waterline in cell B2 is in nautical miles. The resulting dip is output in cell B3 in nautical miles.
The following image shows the spreadsheet dip_short.xls.
Summary for spreadsheet dip_short.xls:
Input cells: B1, C1, B2
Output cells: B3
The spreadsheet distance.xls implements the formula from Bowditch to calculate the distance by vertical angle between the waterline and the top of an object. Select "ft" or "m" in cells C1 and C2, and enter the corrected vertical angle in cell B3. The distance in nautical miles is displayed in cell B4.
The following image shows the spreadsheet distance.xls.
Summary for spreadsheet distance.xls:
Input cells: B1, C1, B2, C2, B3
Output cells: B4
7. Altitude correction for motion of the vessel
The following image shows the spreadsheet alt_move.xls.
Summary for spreadsheet alt_move.xls:
Input cells: A2, B2, C2, D2, E2
Output cells: A6, B6, C6
Intermediate cell: F2
DOWNLOAD TAR ARCHIVE FILE (13 MB)
DOWNLOAD ZIP ARCHIVE FILE (7 MB)
