Interactive Spreadsheets for Celestial Navigation
Dead reckoning
1. Dead reckoning position

Spreadsheet dr.xls computes the dead reckoning (DR) position (row 11) from the previous known position (cells A3 and B3), average speed in knots (cell A7), time interval in hours (cell B7, formatted as a regular floating point number), and course (cell C7).

The following image shows the spreadsheet dr.xls.

Summary for spreadsheet dr.xls:

Input cells: A3, B3, A7, B7, C7
Output cells: A11, B11, C11, D11, E11, F11

2. Dead reckoning fix of Estimated Position along LOP 

When only one line of position (LOP) is available, it is possible to find your estimated position (EP) by using the dead reckoning position (DRP) as a guide.  Spreadsheet dr_fix_lop.xls finds the EP as the point along the LOP which is closest to the DRP. The previous known position is entered in cells A3 and B3, average speed in knots in cell A7, time interval in hours in cell B7 (formatted as a regular floating point number), and course in cell C7.  The LOP is defined as usual by the GP and Ho (cells D3, E3, and F3).  The EP is displayed in row 11.  The distance (in nautical miles) and bearing from the DRP to the EP are shown in cells C13 and F13, respectively.

The following image shows the spreadsheet dr_fix_lop.xls.

Summary for spreadsheet dr_fix_lop.xls:

Input cells: A3, B3, D3, E3, F3, A7, B7, C7
Output cells: A11, B11, C11, D11, E11, F11, C13, F13

The problem preset in this spreadsheet is a variation on the one treated in The Celestial Navigation Mystery Solved by David Owen Bell on p. xliii (Problem 1).

The auxiliary minispreadsheet time.xls can be used to add and subtract time data and also to perform conversions between the HH:MM:SS and hours-decimal formats.

3. Running fix 

If two different celestial bodies are not available for simultaneous measurements, it is possible to obtain the two lines of position by observing the same  body twice within a few hours.  The first observed LOP then has to be displaced by the distance and direction traveled during the time interval between observations.  The spreadsheet running_fix.xls is an extended version of two_body_fix.xls and is used the same way.  Additional input information consists of the average speed in knots (cell A7), time interval in hours (cell B7, formatted as a regular floating-point number), and course (cell C7 - track, measured from true north clockwise).  The solutions are displayed in rows 10 and 12.  The distance traveled (in nautical miles) is in cell D7.

The following image shows the spreadsheet running_fix.xls.

Summary for spreadsheet running_fix.xls:

Input cells: A3, B3, C3, D3, E3, F3, A7, B7, C7
Output cells: D7, A10, B10, C10, D10, E10, F10, A12, B12, C12, D12, E12, F12

4. Set and drift 

The following four spreadsheets solve a number of variations of the set and drift problem.  The preset values are taken from the end of the "Dead Reckoning" chapter in Bowditch.

Calculation of set and drift from the difference between dead-reckoning and estimated positions.

Calculation of the ground speed from the current’s speed and direction (i.e. set and drift) and the vessel speed relative to the water.

Given the set and drift, the vessel's speed and the intended direction relative to ground, this spreadsheet calculates the required vessel course and the resulting ground speed.  If the vessel's speed is too small to counteract the current, an error message is displayed in row 4.

Calculation of the required vessel speed and course from the set and drift and the desired ground speed and track.

5. Closest point of approach 

The spreadsheet cpa.xls computes the closest point of approach (CPA) of another vessel.  This type of computation is useful for collision avoidance.  All bearings and ranges (in nautical miles) are relative to your vessel’s heading.  The calculation encoded into this spreadsheet works with a locally flat Earth’s surface (i.e. it is only valid for small distances) and assumes that both vessels in question move with constant speeds and tracks during the relevant time interval.  The vessel is observed at two different ranges (cells A2 and C2) and relative bearings (cells B2 and D2) separated by time interval entered into cell E2 in the HH:MM:SS format.  From this information the spreadsheet calculates the relative speed of the other vessel in knots (cell A5), range (cell B5) and relative bearing (cell C5) at the CPA, and the time interval between the second observation and the moment of the CPA (cell D5, in the HH:MM:SS format).  If the range at CPA (cell A5) is close to zero, and if the “at” time (cell D5) is positive, the two vessels are headed for collision.

The following image shows the spreadsheet cpa.xls.

Summary for spreadsheet cpa.xls:
Input cells: A2, B2, C2, D2, E2
Output cells: A5, B5, C5, D5



Web Hosting Companies