Five years of Navigation Spreadsheets

Navigation Spreadsheets logo
Navigation Spreadsheets

At the fifth anniversary of our website’s launch we review some of Navigation Spreadsheets functions.  All three examples are taken from the 2014 Nautical Almanac Commercial Edition.

1) Ephemeris (almanac data), Venus GP on 2014 May 5 at 13h 15m 18s (p. 256):


UT: 2014 May 5, 13:15:18

GHA = 58º 58.0’
Dec = S 0º 14.1’



2) Sextant altitude corrections (Venus, p. 259)

Input: Hs = 4º 32.6’
Output: Ho = 4º 17.6’



3) The calculated altitude and azimuth (pp. 279-280)

GP: GHA = 53º    Dec = S 15º
AP: Lat = N 32º    Long = W 16º
to which we add Ho = 30º 30.0’ in order to allow the calculation of the intercept and the plotting of the LOP.



The resulting LOP (intercept 38 NM away, azimuth 223) is plotted with the T-Plotter.




As it was also calculated by intercept.xls this LOP crosses:
the AP’s meridian at 52 NM north of the AP
the AP’s parallel at 56 NM east of the AP



(first published on February 15, 2014)

Many-body celestial fix for a moving vessel

In a recent NavList posting Jeremy provided a set of high-quality real-life observations that can be reduced to a celestial fix for his vessel at a specified moment in time.  The application of Navigation Spreadsheets to this data set results in a very good fix, both by direct computation as well as by plotting.

The following table shows the computed ephemeridescorrected sextant altitudes, line-of position (LOP) characteristics for two choices of an assumed position (AP), and the accounting of vessel motion through dead reckoning (where the mini-spreadsheet time.xls was used to express the time intervals in decimal hours).


The fix at 19:00:00 local time (= 11:00:00 UT, since ZD = -8) is computed to be:
Latitude: N 8º 49.0′
Longitude: E 109º 45.2′

from the many_body_fix.xls spreadsheet:


This location is marked by a black square on the two subsequent plots.

The first plot uses the VSOP plotting sheet (scale 20 nautical miles per inch) with AP at N 9º and E 110º.  The LOPs were drawn with a T-Plotter.  The LOPs in this plot are not shifted by DR.



The second plot “zooms in” with the scale of 1 NM per centimeter to get a more accurate look.  The reference AP is N 8º 50′ and E 109º 40′, which has been individually shifted for each observed celestial body along the vessel’s track (037) in order to account for the motion of the vessel.



The results are excellent, with all LOPs running within a mile of the computed fix.

In the above procedures all LOPs were treated as equally valid.  The fact that there are pairs of LOPs that run nearly parallel to each other complicates matters somewhat.  The GPS fix given by Jeremy:

Latitude: N 8º 48.4′
Longitude: 109º 45.0′

is recovered almost exactly (both by computation and by plotting) if only Jupiter, Rigel, and Fomalhaut LOPs are used.

For comparison, Jeremy’s position is marked by the square in Google Earth at the top of this post.


(first published on March 8, 2013)

Celestial line of position with the T-Plotter

The recent addition of a protractor to the T-Plotter makes it a fully self-contained tool for plotting of celestial LOPs, as is shown in this demonstration video.  It is important to note, that the use of the T-Plotter is not limited to the scale of 20 nautical miles per inch printed on the instrument.  It is always possible to read off the equivalent number of tics from the latitude scale of any chart according to the picture below.



(first published on December 20, 2012)

LOP plotting using metric graph paper

Some time ago Antoine “Kermit” Couëtte kindly responded to my question about chart scales by stating that he used 1 cm per nautical mile.  In this post I show how that can be achieved using metric graph paper.  As an illustration I pick the Antares line of position (LOP) from the “many-body fix” example (vessel motion is ignored in this post).

Here we really need to “zoom in” in order to use this scale, so I changed the latitude of the assumed position AP by half a degree to N 31º 30′.  The AP longitude stayed the same at W 15º.


The spreadsheet intercept.xls calculates several characteristics of this LOP.  Besides the usual intercept and azimuth, it also gives its intersections with the AP’s parallel (cell F12) and meridian (cell F15).  This information allows the quick plotting of the LOP simply by marking and connecting these two points.  This technique will not work for LOPs that run close to the cardinal directions, but I believe that it is a good option to have available nonetheless.


After the two points F12 and F15 were connected thus forming the LOP, several measurements were made on the chart to show internal consistency of all the other data computed by the intercept.xls spreadsheet.  The intercept is indeed AWAY (cell E6) with azimuth (cell F6) 152º and it is 3.4 nm (cell D6, allowing about 1 mm for plotting imperfections that only translate to 0.1 nm, which does not degrade the precision achievable in celestial navigation).

intercept: 3.4 nm away from the geographical position (GP) of the observed body

azimuth: 152º toward the GP

LOP direction: 242º and 62º – both perpendicular to the azimuth

This last piece of information allows for yet another way of plotting this LOP using parallel rules on a chart or a plotting sheet with a preprinted compass rose, as it is shown here.


(first published on November 16, 2012)

T-Plotter applications

In a recent posting to NavListGreg Rudzinski has shared his novel idea of using the T-Plotter in conjunction with a square protractor.  He illustrated the steps of his alternative procedure of plotting a celestial LOP obtained by the intercept method with the following photographs:







(first published on June 3, 2012)

T-Plotter Blank

The “Blank” version of the T-Plotter facilitates the plotting of LOPs on charts of any scale.

Distances are marked directly on the plotter with a dry-erase marker (not included).  For more information click here and view the demo video here.

(first published on March 25, 2012)

T-Plotter Basic

According to the intercept method of Marcq St. Hilaire a celestial line of position (LOP) is plotted on a chart as the line perpendicular to the azimuth line at the intercept distance toward or away the geographical position (GP) from the assumed position (AP).  This can be accomplished with the T-Plotter®- a device consisting of two mutually perpendicular arms: the azimuth arm that is lined along the azimuth line, and the plotting arm along which you can plot the LOP.  The use of the T-Plotter reduces the clutter on the chart by eliminating the need to also plot the intermediate (and usually not needed) azimuth line.

The T-Plotter Basic model is imprinted with a grid that fits the VP-OS (Universal) Plotting Sheets, on which 20 nautical miles are represented by 1 inch.  Click here to view a demonstration video of how T-Plotter Basic can be used to plot celestial LOPs. For additional specification and ordering information, visit:

(first published on January 28, 2012)

Two-body fix (Santa Barbara, 16 July 2011)

A recent trip to Santa Barbara, California, presented me with an opportunity to do some sights and calculations. In the following example I took a series of Sun sights in the morning and a single sight in the afternoon.  The four morning sights were averaged to produce a single effective data point, whose LOP was then crossed with the LOP from the afternoon sight to obtain a fix.

Observation point:
Google Earth coordinates: Santa Barbara Sailing Club beach
N    34º 24.18′    i.e.    34.403
W 119º 41.64′    i.e. -119.694

These coordinates were used as the “assumed position” (AP) in the subsequent calculations of intercepts and azimuths.

Sun semidiameter (SD) = 15.7′

Sextant: Davis Mark 15

16 July 2011 (Sun: morning):  T=25 ºC,  P=1011 mb,  Index Correction=+8.0′,  Height of eye=6 ft
UT               Hs               Ho               GHA            Declination   Intercept    Azimuth
17:42:30      55° 48.2′     56° 08.9′      84° 06.4′      N 21° 19.9′       0.4A         103.3
17:45:20      56° 23.4′     56° 44.1′      84° 48.9′      N 21° 19.8′       0.8T         103.9
17:47:50      56° 51.6′     57° 12.3′      85° 26.4′      N 21° 19.8′       1.0A         104.4
17:50:30      57° 22.4′     57° 43.1′      86° 06.4′      N 21° 19.8′       2.1A         105.0

The spreadsheet average2.xls results in a simple average of these four observed altitudes:


that is:
UT               Hs               Ho               GHA            Declination   Intercept    Azimuth
17:46:32       —               56° 57.1′      85° 06.9′      N 21° 19.8′       0.6A         104.1

The single afternoon sight was (this time the sextant’s mirrors were adjusted to eliminate index error):
16 July 2011 (Sun: afternoon):  T=26 ºC,  P=1010 mb,  Index Correction=0.0′,  Height of eye=6 ft
UT               Hs               Ho               GHA            Declination   Intercept    Azimuth
21:18:20      69° 00.6′     69° 13.6′     138° 03.7′     N 21° 18.4′       1.6T          235.8

The two LOP intersections can be computed either with spreadsheet lops.xls or two_body_fix.xls.


Solution #1 is relevant in our case:

N    34º 22.8′
W 119º 42.8′

This fix is only 1.7 nm bearing 215 from the Google Earth coordinates, as seen both from:


and a Google Earth measurement:

Overall I think I can be reasonably happy with these results and the intercepts I got. Considering the difficulties I had with the index error determination I was in fact a bit worried before I started the calculations. The error of fix and the standard deviation of intercepts are interestingly similar at about 2 nm. Using this value as the “Scatter” parameter in the weighted least-squares fitting procedure (average2.xls: fitted, not precomputed slope), all weights came out equal, so this procedure resulted in calculating the simple average of UT’s and Ho’s.

(first published on September 1, 2011)

Lunar occultation of Aldebaran

The Wikipedia entry for the star Aldebaran contains the following image:

Based on the information on this page (e.g. image was created in July 1997) and after some trial and error with Excel (see screenshots below) I came up with the following plausible coordinates in time and space at which this image may have been created:

New Orleans area:   N 30º W 90º
UT: July 29, 1997,    10h 08m 30s

This really is only one out of many possible solutions, which I did not investigate further.  I neglected refraction which would have a small effect for such a tiny lunar distance (center-to-center topocentric LD = Moon SD = 15.5′) and the overall achievable accuracy in this exercise (no obviously visible refractional flattening of Moon’s disk).  Parallax is important (center-to-center geocentric LD = 34.4′)

Accompanying data look consistent with everything else:
The Moon age (25 days, “waning crescent”) and phase (23% or about 1/4 illuminated)
Local time (UT-6h) => around 4am, about an hour before sunrise (“predawn”)

The two bodies would have appeared due east at an altitude of roughly 34 degrees.










(first published on May 22, 2011)