BLOG.NAVIGATION-SPREADSHEETS.COM

Compact T-Plotters

2012-05-09T03:03:13Z

The selection of available T-Plotters now includes smaller, "Compact" models. For additional info visit:
http://www.t-plotter.com

T-Plotter Compact

T-Plotter Compact Blank

Star finding

2012-04-08T17:09:59Z

A customer recently asked whether there is a spreadsheet in this suite that would show which celestial objects are available for observation at a given time from a given location. While the short answer to this query is "no", this capability does exist in the combination of an almanac spreadsheet with intercept.xls. If the computed altitude Hc is positive, then the object is above the horizon.

That said, at least for stars one can use the what_star.xls spreadsheet for that purpose. You enter the UT and your location and then scroll to the right to column AI. If the computed altitude (Hc) is positive, then that star (star names are copied into column AF) is above the horizon. Column AH has the star's azimuth.

In the following example Acrux is above the horizon, whereas Acamar and Achernar are not. In this manner you can scroll down in the spreadsheet to inspect all 57 main navigation stars for availability.

T-Plotter Blank

2012-03-25T22:35:11Z

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.

T-Plotter Basic

2012-01-28T23:53:36Z

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:

http://www.t-plotter.com/

Set and drift

2011-11-11T19:28:18Z

The Sailings calculations determine the course for a vessel to follow in order to get from the point of Departure to the Destination. This is course with respect to ground which can differ from the course to steer if the vessel is deflected sideways by currents and/or winds. This leads to a class of "set and drift" problems described, for example, in the Dead Reckoning chapter in Bowditch. These problems are often solved graphically by plotting procedures. As for the equivalent numerical solution we can do the following.

The essence of these problems is the relationship between three vectors in which the ground speed is the vessel's speed relative to the water plus the set and drift vector. The relevant geometry occurs in 2-D, so this relationship translates into two equations for the vectors' components. Therefore, given four pieces of information on input we can solve for the remaining two.

If these two numbers both pertain to the same unknown vector (e.g. its magnitude and direction), then two Cartesian-component equations provide the solution to the problem by direct addition or subtraction of the other two (fully specified) vectors. This is the case for spreadsheets:

http://www.navigation-spreadsheets.com/dead_reckoning.html#set_and_drift

ground_speed.xls:
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.

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

The situation is a little bit more complicated for:

course_to_steer.xls:
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.

in which the two unknowns are distributed between two partially known vectors. This problem can be solved in the following steps encoded into the spreadsheet:
1) Decompose the current's vector into components parallel and perpendicular to the prescribed "Sailings" (ground) direction.
2) Reverse the sign of the perpendicular component; this becomes the perpendicular component of the vessel's speed with respect to water. That way the deflecting effect of the set and drift is neutralized.
3) Use the Pythagorean theorem to determine the component of the vessel's speed (w.r.t. water) parallel to the ground direction. The known vessel's speed w.r.t water is the hypotenuse and the result of step 2) is one of the sides.
4) Convert the vessel's speed's now known two components (along with the ground direction) into course to steer.
5) Add the two parallel speeds' components in order to obtain the ground speed. If this problem has two mathematically good solutions, this picks the "faster" one.

This problem does not always have a solution. If the vessel's speed w.r.t. water is less than the current's perpendicular component, the vessel is not fast enough to compensate for the deflection off course. If the current's parallel component is negative (e.g. a strong headwind in case of an aircraft) and larger in absolute value than the parallel component of the vessel's speed w.r.t. water, then the vessel is not fast enough to progress toward its destination. In either case, the spreadsheet zeroes out the output and displays an error message.

Plotting of a celestial LOP

2011-10-29T01:41:25Z

The recently extended spreadsheet intercept.xls:

can be used in alternative ways to plot the same line of position (LOP), as shown in this demonstration video.

Almanac data in 2012

2011-09-24T18:13:33Z

Comparisons with the newly published Nautical Almanac show that our spreadsheets are good for 2012 without the need for any updates.

http://www.navigation-spreadsheets.com/almanac_data.html

Mars data for December 31, 2012, UT 23h 00m 00s:

mars.xls:

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

2011-09-02T04:35:59Z

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:

average2.xls:

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.

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:

sailings.xls:

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.

Noon curve: Santa Barbara (17 July 2011)

2011-07-31T23:12:22Z

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 not long before the Local Apparent Noon (LAN). I was unable to stay long enough to observe the actual meridian upper transit of the Sun but the data were still suitable for a noon-curve construction by extrapolation and thus establishing the latitude and longitude of my location with decent enough accuracy.

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

17 July 2011 (Sun: just before LAN): T=28 ºC, P=1018 mb, Index Correction=+8.0', Height of eye=10 ft
UT   Hs        Ho         GHA        Declination   Intercept    Azimuth
19:33:30    74º 38.4'    74º 58.8'    111º 50.0'    N 21º 09.0'    4.0A     150.4
19:35:30    74º 55.4     75º 15.8'    112º 20.0'   N 21º 09.0'    1.1T      152.0
19:37:55    75º 09.4'    75º 29.8'    112º 56.2'   N 21º 09.0'    1.5T            154.1
19:40:30    75º 16.2'    75º 36.6'    113º 35.0'   N 21º 09.0'    5.1A        156.3
19:43:00    75º 34.0'    75º 54.4'    114º 12.5' N 21º 08.9'    0.9T        158.5
19:46:00    75º 45.0'    76º 05.4'    114º 57.5'   N 21º 08.9'    0.8A        161.3
19:49:50    75º 58.6'    76º 19.0'    115º 55.0'     N 21º 08.9'    1.0A          164.9
19:53:30    76º 08.0'    76º 28.4'    116º 50.0'   N 21º 08.9'    2.1A            168.5
19:57:30    76º 14.8'    76º 35.2'    117º 50.0'     N 21º 08.8'    3.4A            172.5

Observed altitudes (Ho) were obtained from the recorded sextant altitudes (Hs) with alt_corr.xls; see the first data point as an example (these were lower-limb observations, hence the SD correction is positive).

alt_corr.xls

The Sun GHA, Declination, SD, and (later) Equation-of-Time values came from sun.xls. The intercept and azimuth were calculated with intercept.xls. The intercept distances are small as expected, since they were calculated using the known position as the AP. The fact that the intercepts are not exactly zero is a measure of the quality of the instrument and the skill of the person using it. The azimuths approach the meridian passage value of 180º but stop just short of it due to reasons explained above.

In the following image we can indeed see a rather convincing arc that would peak shortly after 20h UT. This plot has the Ho's on the y-axis.

Before the actual fitting these Ho's are further adjusted to account for the Sun's hourly declination change of -0.4'. In addition, the noon_motion.xls spreadsheet is also capable of addressing the construction of this curve on a moving vessel; in this case the speed is zero since I made the observations from a fixed location.

noon_motion.xls

The results are computed by noon_motion.xls to be:

N    34º 29.1'
 W 119º 24.5'

One generally expects getting a very accurate latitude value and not-so-great longitude value from meridian-transit observations. In the past I have observed that the parabolic fitting employed by these spreadsheets works very well if the data actually straddle the culmination point. This computed result is not as good but it is still reasonable, especially since it came from a data set that stopped short of LAN and hence had to be extrapolated.

Summer solstice 2011 (northern hemisphere)

2011-06-22T05:42:13Z

Today on June 21 we mark the summer solstice in the northern hemisphere. This is the day when the Sun reaches its maximum northern declination (Tropic of Cancer). Given the 0.1' precision displayed for celestial navigation purposes, this declination appears constant for an extended period of time around the exact moment of the solstice. In order to pinpoint that special moment down to a minute (or even a second), we would have to display more decimal places of the Sun's declination in order to spot the instant when it reaches its maximum.

Alternatively, however, we can look further down the sun.xls spreadsheet and look at the intermediate result of the Sun's right ascension ("Alpha"). The input UT in row 2 has been adjusted so that Alpha = 90º (or 6 hours, in astronomers' lingo). This means that the Sun has completed exactly one quarter of its annual roundtrip starting at Alpha = 0 (the vernal equinox). This places the moment of the 2011 northern summer solstice on June 21 at 17h 16min Universal Time (10h 16min U.S. Pacific Daylight Saving Time).

Greenwich Hour Angle of stars

2011-06-19T01:30:28Z

A "sight" in celestial navigation consists of measuring the body's altitude with a sextant and marking the time of that observation using a chronometer. The celestial "line of position" (LOP), along which the observer is located, is a circle whose radius (called "zenith distance") is deduced from the sextant altitude measurement. The instant of the observation, expressed in Universal Time (UT), specifies the body's "geographical position" (GP) which is the center of this LOP. A crossing of this LOP with at least one other such LOP results in a celestial fix on the observer's position.

The GP is a set of two numbers: the declination (counterpart to latitude), and the Greenwich Hour Angle (GHA) which is analogous to longitude. The declination is an angle ranging from -90º (South Pole), through 0º (Equator) to +90º (North Pole). The GHA increases westward starting from 0º at the Prime (Greenwich) Meridian and ending at 360º upon reaching the Prime Meridian again after one full round-trip around the Earth. The GP coordinates for the main navigation bodies are precomputed and published in almanacs for future use.

Stars differ from the bodies of the Solar System in that they have almost negligible proper motion relative to the Earth. As a result, their declinations are nearly constant and their GHA evolution with UT is almost entirely due to Earth's rotation alone. In other words, the stars are practically "fixed" to their very nearly constant positions on the celestial sphere. This allows almanac publishers to save a lot of space using the following scheme.

Instead of tabulating the GHA of each navigation star in some increments of UT (like it is done for Sun, Moon, and planets), this value is computed by adding two numbers:

GHA_Star = GHA_Aries + SHA_Star

"Aries" (i.e. the point of vernal equinox) represents the chosen "Prime Meridian" on the celestial sphere and SHA (Sidereal Hour Angle) is the star's constant westward position relative to Aries. Since SHA does not change with UT, almanacs can be made much more compact by tabulating each star's SHA just once and placing all UT dependence into the single column of GHA_Aries. This arrangement is also used in our aries_stars.xls spreadsheet, as shown in the example below (all numbers are in degrees decimal):

For UT of January 1, 2012, 12h 00m 00s, we have GHA_Aries (280.56).

To get GHA_Acamar we add its SHA (315.31) to GHA_Aries (280.56), resulting in 595.87. From this value we subtract 360.00 to bring the GHA to its conventional range and obtain GHA_Acamar (235.87).

To get GHA_Achernar we add its SHA (335.45) to the same GHA_Aries as above (280.56), resulting in 616.01. From this value we subtract 360.00 to bring the GHA to its conventional range and obtain GHA_Achernar (256.01).

Lunar occultation of Aldebaran

2011-05-22T19:48:18Z

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

http://en.wikipedia.org/wiki/File:Occultation.jpg

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: 30N 90W
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.

moon.xls:

aries_stars.xls:

ld_prec.xls:

intercept.xls:

Composite sailing

2011-02-02T06:13:00Z

If the computed great-circle route were to reach into higher than desired latitudes, it is possible to eliminate that problematic section of the voyage with a composite sailing calculation. Such a path consists of three sections:

1) great-circle route from Departure to the chosen Limiting parallel,
2) parallel sailing along the Limiting parallel,
3) great-circle route from the Limiting parallel to Destination.

The two great circles from parts 1) and 3) are chosen in a way that places both of their vertices on the Limiting parallel.

The example below uses the same San Francisco-Yokohama trip from our earlier SAILINGS blog entry. Here the Limiting parallel is set at N 40d, below the original great-circle vertex latitude of N 48d 03.7'. The spreadsheet is composite.xls.

Sailings

2011-01-15T19:58:00Z

The term "celestial navigation" (sometimes called "astronavigation") typically evokes in our minds an image of a sextant and comes along with terms like Universal Time, line of position, nautical almanac, intercept, etc. Indeed, those are among the topics discussed in our spreadsheet project so far:

http://www.navigation-spreadsheets.com/

With these concepts, techniques, and gadgets you can establish your position from astronomical observations. You can also keep track of your changing position during a trip, for example by bringing the method of dead reckoning into consideration. Until now, however, an important aspect of navigation (which is relevant whether you use celestial or not) has not been addressed by our suite: trip planning, also known as sailings calculations.

We are pleased to announce that we now provide this additional capability in the latest extension to our suite. Determining the direction (course) in which to sail (and knowing in advance the length of the journey) is an essential skill that any navigator must have. For short trips one may directly measure the constant rhumb-line course on a Mercator chart for the path that connects the point of departure with the destination. However, the bigger the separation between departure and destination, the more extra distance is associated with the rhumb-line path compared to the shorter great-circle path, especially in higher latitudes. The problem is that attempting to follow the requisite great circle is very difficult since it requires a continuous adjustment of heading.

Thus, on the one hand, the great-circle route (orthodrome) is shortest but it is difficult to steer. On the other hand, the rhumb-line route (loxodrome) can be well followed along its constant course but it is longer. Each choice thus has a strength that is a weakness in the other one. A solution to this dilemma is outlined, for example, in Bowditch which recommends a hybrid path combining the advantages of the two sailing possibilities. Here the starting point in developing the sailing plan is the shorter great-circle route from departure to destination, but then along that path one identifies waypoints (separated, for example, by 5 degrees of longitude) between which the vessel is to follow easier-to-steer rhumb lines.

We illustrate such a calculation using as an example a trip from San Francisco (USA) to Yokohama (Japan) borrowed from Bowditch. This classic publication demonstrates the idea graphically using the chart of the North Pacific Ocean shown in two different projections.

First, the great-circle path from San Francisco to Yokohama is found as the straight line connecting the two cities on the gnomonic projection chart. We mark the waypoints as this path crosses meridians separated by 5 degrees of longitude. Second, these waypoints are translated onto the Mercator projection chart on which they are connected by straight-line segments representing rhumb lines. The constant course headings within each successive pair of waypoints can be directly measured on this chart. Our new spreadsheets perform this exact same function (plus the distance calculations) with even higher accuracy, because they are not affected by the inaccuracies of physical plotting on a chart.

The problem to solve is fully specified by the coordinates of the departure and destination locations. We have:

San Francisco:
Lat: N 37d 48.0'
Lon: W 122d 33.0'

Yokohama:
Lat: N 34d 42.0'
Lon: E 140d 06.0'

These coordinates enter the spreadsheet sailings.xls in row 2.

The use of this spreadsheet is shown in this YouTube demo video.

The differences between the calculated great-circle and rhumb-line paths are substantial. The rhumb line is longer by over 200 miles and its (constant) heading is south of west, while the initial great-circle course is north of west sailing into higher latitudes first. Row 11 displays the coordinates of the vertex, which is the point along the great circle closest to the Pole. The spreadsheet also shows the (relatively minor) differences arising from the use of a perfectly spherical or slightly flattened ellipsoidal model of the Earth surface in the calculation.

For our purposes the main result of this spreadsheet is the initial great-circle course displayed in the yellow cell C6. This number, combined with the departure coordinates, completely defines the great circle. The value is shown with three decimal digits (302.240) in order to cut down numerical round-off errors once it is copied as input into the next spreadsheet: waypoints.xls.

Here, the coordinates of San Francisco and this initial course from sailings.xls enter in row 2. Then, in column A starting in row 11 we begin entering the longitudes of each waypoint. All great circles (except those running across both Poles) intersect every meridian exactly once. The calculated latitude of each waypoint is displayed in columns C, D, E. The rhumb-line distance and course from the previous waypoint (using the flattened Earth model) is shown in columns F and G.

The initial course is north of west so the latitudes of the subsequent waypoints increase at first. The courses between them, however, progressively turn away from north and at longitude W 170d (close to the vertex, row 7) it is essentially due west. The course then heads ever more southward as the path descends back to lower latitudes toward Yokohama. Figure 2404 in Bowditch shows the E 150d waypoint at latitude N 40d, for which waypoints.xls calculates N 39d 45.5'. The last waypoint is the destination itself (Lon: E 140d 06.0') with the correctly reproduced latitude (N 34d 42.0'). This hybrid path is still longer (cell F2) than the pure great-circle route, but not by much, and it is easier to steer.

Lunar eclipse, December 2010

2010-12-21T05:46:00Z

Here is some info pertaining to today's lunar eclipse. You can see that the Earth is indeed right between the Sun and the Moon, and that the eclipse will be nicely visible (weather permitting!) from western United States.

1) On the Sun-Earth-Moon positional arrangement:
a) the Sun and Moon declinations are very close to being equal in value and opposite in sign/hemisphere, and,
b) their Greenwich Hour Angles (GHA) differ by 180 degrees, which places the two bodies on opposing meridians.

The Moon is of course 15 days "old" and its phase is "full" (100% of the disc illuminated).

2) On the visibility of the eclipse:
The Moon subpoint (a.k.a. GP = geographical position) is:
Latitude (from declination): N 23 degrees 44.9' (very close to Tropic of Cancer)
Longitude (from GHA): 124 degrees west of Greenwich
This location in the Pacific Ocean rather close to Baja California makes this eclipse visible from our area.

The Sun is essentially on the Tropic of Capricorn (declination S 23 degrees 26.2') making this a rare event when an eclipse coincides with a solstice.

The time given is Greenwich time (Universal Time, UT) which is 8 hours ahead of our own Pacific Standard Time.

LATER UPDATE: A picture taken by a friend of this blog:

Sight reduction of a Moon observation

2010-08-17T13:37:00Z

The data for the following example are from John Karl's "Celestial navigation in the GPS age" (First Edition, 2007), pp. 63-65. With three spreadsheets we reproduce the computations for the sight reduction of the Moon observation presented in Figure 6.4 on p. 64. Given the dead-reckoning (DR) position and the recorded time of observation (UT), the Moon sextant altitude (lower-limb) is reduced to intercept distance and azimuth needed to plot the associated line of position (LOP) according to the intercept method of Marcq St. Hilaire.

Input data:
Body: Moon, lower limb
Date: 11 May 2005
UT: 02^h 19^m 14^s
Hs: 25º 21.6'
Sextant index correction: -3.3'
Height of eye: 9 feet
assuming standard atmospheric conditions
DR position: S 34º 13', E 161º 43'

moon.xls:

Input:
Date: 11 May 2005
UT: 02^h 19^m 14^s
In cells A2-F2 entered: 2005 5 11 2 19 14

Output:
GP (row 5)
GHA: 182º 25.4' (same as 181º 85.4' in the book)
Dec: N 27º 42.9' (difference of only 0.1')

Semidiameter (SD, cell A8): 15.0'
Horizontal parallax (HP, cell C8): 55.1'

Note that there is no need for increments and corrections (v or d Corrⁿ).

This "almanac" spreadsheet for Moon is available here for a free download.

alt_corr.xls:

Input:
Height of eye (cells E1, F1): 9 ft
Using standard conditions for temperature and pressure:
Pressure (cell E2): 1010 mb
Temperature (cells E3, F3): 10 degrees Celsius
HP (in arcminutes from moon.xls cell C8): 55.1 entered in cell E6

Sextant altitude (Hs): 25º 21.6', entered as 25 216/600 in cell B1
Index correction: -3.3', entered as - 33/600 in cell B2
Artificial horizon was not used: entered N in cell B4
SD (in degrees from moon.xls cell A8): entered (+)15/60 (positive for lower limb observations) in cell B11

Output:
Observed altitude (Ho) in cells B12-B14: 26º 18.2' (difference of only 0.1')

Note the intermediate result for apparent altitude (Ha) in cells B6-B8: 25º 15.4' (same as in the book)

intercept.xls

Input:
Assumed position (AP) is taken to be the dead-reckoning (DR) position:
Latitude: S 34º 13', entered as -34 13/60 in cell A2
Longitude: E 161º 43', entered as (+) 161 43/60 in cell B2

GP of Moon from row 5 of moon.xls:
GHA: 182 254/600 entered in cell C2
Dec: (+) 27 429/600 entered in cell D2

Ho from alt_corr.xls cells B13, B14: 26 182/600 entered in cell E2

Intermediate results:
LHA (cells F2, F3): 344º 08.4' (same as in the book)
Computed altitude (Hc in cells A6-C6): 26º 16.4' (difference of only 0.1')

Main results:
Intercept (cells D6, E6): 1.8 Toward (difference of only 0.2')
Azimuth (cell F6): 15.7 agrees with 16 from the book after rounding

worksheet.pdf:

The data from this example are summarized in the worksheet below; input data are marked as green, and spreadsheet results (some of which are relayed as input for subsequent calculations) are blue.

Project history III

2010-05-29T15:51:00Z

The web browsing that I had done during this process did not lead me to any explicit solutions (such as finding someone offering a complete suite of Excel spreadsheets for celestial navigation computations…). I did find, however, a discussion list of like-minded people that I promptly joined. This "NavList" has been and continues to be a great source of wisdom. Recently a very good overview of NavList was published here:

http://columbianewsservice.com/2010/03/they-sail-their-ship-alone/

What is especially valuable is that there are number of members who are accomplished sailors, scientists, and sometimes both, who are a treasure trove of knowledge and experience. On a number of occasions they presented real-life data they themselves took while sailing, which enabled me to successfully verify the performance of the suite. In fact, recently one gentleman serving on a merchant ship was in the Indian Ocean taking sights with his sextant and has put my noon spreadsheets to work.

Through NavList I came to learn about John Karl, a physicist, artist, and sailor whose excellent book "Celestial Navigation in the GPS Age" I subsequently obtained. Finally I had a reference whose style was more in sync with my own thinking; in fact, some of the things I had already done with Excel were even suggested in the book as exercises for the reader! But the most valuable piece of information in this book for me was a reference to another book titled "Astronomical Algorithms" by the Belgian astronomer Jean Meeus.

The spreadsheets that I had developed up to that point had to do with sight-reduction, that is calculating one's position by solving the relevant geometry on the surface of the Earth. This however was not a self-contained suite yet, as it required astronomical data as input. This information, most notably the positions of the observed celestial bodies at the moment of measurement, is precomputed in advance and published in almanacs. Meeus's book clearly explained how this can be done and even provided analytic formulas that describe our solar system. The word analytic (as opposed to numeric) is important because this way it is possible to describe the planetary orbits with formulae of very broad validity rather than tables whose size scales with the chosen time period and which would require search and interpolation procedures. Furthermore, the accuracy of these equations was way better than what is realistically required (and achievable) for celestial navigation purposes.

And thus the idea came whether it would be possible to complete the suite by encoding these formulae into Excel as well. The formulae were quite large, with lots of terms, and there were a number of steps to keep track of. Therefore, I decided to first write a program in Fortran, a computer programming language with which I am well familiar. After I ran the program and convinced myself that the results agree with the official Nautical Almanac, I encoded the same calculations into Excel using the now verified Fortran program as a guide. This took a fair amount of time, several weeks in fact. Some of the spreadsheets exceeded 1 MB in size due to the number of terms included. But it worked and by the end of June 2009 we rolled out a major extension to the suite, now including these new "almanac spreadsheets." I was pleasantly surprised to see that Excel was able to handle this kind of stuff. Think about it, a software tool designed for accounting, inventory and whatnot, can be actually used to predict the positions of planets and stars with accuracy better than one minute of arc! I just think that's awesome, in a freaky-geeky kind of way.

Excel also has the advantage that it (or its sufficiently compatible alternatives and competitors…) are available on pretty much any computing platform. The spreadsheet app then serves as a kind of "virtual machine" in the spirit of Java. Thus there is only one version of Navigation Spreadsheets, and it can be run on Windows, Mac, Linux, even on a smartphone, without modification. On the website there are links to YouTube demonstration videos in which I used an iPod Touch.

So that's the summary of how this project came to be and what it is about. I want to come back to give occasional updates, maybe give an example or two, and provide additional historical details about the suite from time to time. Stay tuned.

Project history II

2010-05-15T02:13:00Z

And so the learning process began. I knew about latitude, longitude and a few other things but then came zenith distance, navigation triangles, assumed position, the intercept... All these were new terms with all kinds of formulae, rules, and procedures attached to them. I read about the noon sun and its special significance. At noon, the sun reaches its maximum altitude for that day and from the associated sextant measurement one can determine one's latitude via simple arithmetic. The slight problem with that is that marking the moment of noon precisely is a bit difficult in practice. Thus the next topic in my little textbook was the concept of the noon curve. Instead of making a single measurement, here one tracks the sun before noon as it rises, then after noon as it descends and extracts the peak of that curve. Having multiple measurements this is automatically more accurate than just trying to mark the moment and taking a single observation when you think you have noon.

At this point I figured that the noon curve method could be easily and rigorously implemented with Excel; after all it's just a quadratic (parabolic) fit, for which Excel is already well equipped. With that in mind I constructed the first spreadsheet, noon_curve.xls. This spreadsheet alone, however, did not quite lead me to "go public" with this project; at this point this was all still limited to my own amusement.

The idea of starting a website and making spreadsheets available to others came after some thinking about the position fix from two altitudes. The "industry standard" method of Marcq St. Hilaire known as the intercept method has proven its merits for over a century. However, what bugged me about it was its reliance on the so-called "assumed position" which was something that seemed semi-arbitrary and not very intuitively clear to me. I was certain that one should be able to calculate the crossings of the two circles of equal altitudes directly without assuming anything. Searching on the Internet revealed nothing on the subject, so I dug into the geometry of the problem, came up with a solution, which I then encoded into another spreadsheet named lops.xls (LOPs stands for two intersecting Lines Of Position marking the position fix.) I was quite sure that I was simply rediscovering something that was already known but I could not find it in any materials that I had at that moment. Now I thought that I had something other people might find useful and as a result I developed the first version of the Navigation Spreadsheets website. It was only fitting that my wife contributed to the website's design, after all this was all her fault! The site was launched on Thursday, February 19, 2009 and not too long after that first customers began to trickle in. (to be continued)

Customizing the suite

2010-05-06T02:21:00Z

The spreadsheets are open software in the sense that all their contents are exposed to the user. Since they can be unlocked without a password, this allows users to customize the spreadsheets to their preferences. We have heard from customers who are in fact doing so by combining several spreadsheets into single multitab documents. This automatizes the transfers of data between separate calculations and bypasses the need of manually copying data between spreadsheets. For portability (and other) reasons, I decided early in the project's development not to bundle separate calculations into multisheet workbooks in this manner, since not all Excel-like apps support this feature. However, if your app indeed can handle multisheet documents, feel free to create new such spreadsheets to better suit your needs. We'd always like to hear from you about your experiences with the suite. And who knows, if you share your insights on this blog, that may get you in contact with other fellow Excel celestial navigators, so you can exchange ideas, and make even more progress!

Project history

2010-05-03T06:52:00Z

I suppose that the official beginning of this project could be assigned to Wednesday, November 12, 2008, late afternoon Pacific Standard Time. On that day my wife and I were visiting the town of Dana Point on the California coast, where she grew up. She showed me her childhood home, her high school, cool places to hang out. In the evening we drove down to the harbor. As we were walking on the beach, we encountered two fellows, one equipped with a sextant, who were observing the sunset. "Right on the money," one of them said. He was certainly correct to make that observation, at least as far as I was concerned. You see, I had been under standing orders from my better half to choose a Christmas present for myself. For weeks now I had been totally clueless on this subject, but now, thanks to those two guys on the beach, my conundrum was immediately resolved. And thus at Christmas I found myself a proud owner of a new Davis Mark 15 sextant, some study materials and plotting utensils, and an artificial horizon. Since I already had a radio-controlled clock that could serve as my "chronometer" I was ready to start learning and practicing the principles and procedures of celestial navigation. (to be continued…)