Interactive Spreadsheets for Celestial Navigation
Sextant altitude corrections
1. Altitude corrections

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

5. Dip short of the horizon 

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

6. Distance by vertical angle 

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 spreadsheet alt_move.xls calculates the effective observed altitude associated with a line of position that is advanced or retarded by dead reckoning.  In this technique of compensating for the vessel motion the assumed position is unchanged and only the final adjusted LOP needs to be plotted.  Enter the original observed altitude and azimuth from spreadsheet intercept.xls in cells A2 and B2.  Enter ground speed and course made good in cells C2 and D2.  The time interval in cell E2 is positive to advance the LOP and negative to retard the LOP.  The (signed) distance traveled in nautical miles is displayed in cell F2.  Reenter the adjusted observed altitude from row 6 in cell E2 of intercept.xls to obtain the new intercept.

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



Web Hosting Companies