AVERAGING OF SIGHTS: 1. PRECOMPUTED SLOPE
© Navigation Spreadsheets - All rights reserved.
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.
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