Interactive Spreadsheets for Celestial Navigation
Introduction



In our world of ever expanding technology, many people have reached to the past to rediscover the traditions of navigation on the high seas by using the sun, the moon, the planets, and the stars. Even today in the age of the Internet, telecommunication satellites, and the Global Positional System (GPS), there still are people who have reconnected with the earth and the sun through the art and science of celestial navigation. Just as sailors did in the days of tall ships and billowing sails, we also are able to determine our position on the earth by looking at the sky armed with just a sextant, a chronometer, and some tables (click here for a condensed description).

The spreadsheets available through this website are designed to increase the accuracy, reliability, and the speed with which you can derive your position from  observations of the heavens.  First, you take a sight with a sextant, make the appropriate corrections to the measured altitude, and retrieve the necessary astronomical data from an almanac spreadsheet.  Then you enter the data into the appropriate sight reduction spreadsheet - and you're done!  The use of these spreadsheets finds a middle ground between manually doing all the steps needed to plot your line of position on a chart and simply pushing a button to read your location on a GPS receiver.

Our spreadsheets are programmed to provide and process several types of sight data needed in celestial navigation:
For the low price of only US$9.99 you may download all twenty-seven spreadsheets plus the PDF version of the manual with all of the information contained on this website.  Click here to download a free sample (calculation of almanac data for the Moon).



A word about our spreadsheets:

You may consider using these spreadsheets because they are:
  • Accurate: The numerical accuracy of the results is adequate for celestial navigation purposes.  The largest difference seen in our tests versus published almanac data was 0.2'.  The polaris.xls spreadsheet is an exception in which we have seen differences up to 2.0' in the SHA of Polaris.  Even so, this does not degrade the accuracy of the results from that spreadsheet.  The numerics of modern spreadsheet applications renders errors in the sight reduction spreadsheets negligible.
  • Fast and robust: You can process your sights in seconds with the chance of human-introduced arithmetic errors virtually eliminated.
  • Self-contained: This collection of almanac and sight reduction tools provides a complete package for the processing of your sights from start to finish.  Furthermore, after they are downloaded you may use them whether you have an Internet connection or not.  This may come handy in the middle of the ocean.
  • Easy to use: The spreadsheets are self-explanatory.  If you are already familiar with celestial navigation procedures you will be able to use them right away.  We also provide a manual with a detailed description of their functionality.
  • Affordable: We are not the only ones who consider the $9.99 "a very small, reasonable fee."
  • Safe: Our spreadsheets use only  arithmetic, logical, and trigonometric operations and functions.  There are no macros, so you can be confident that the spreadsheets contain no viruses.
  • Secure: The spreadsheets are protected against accidental changes of data and formulae by locking all but the input cells.  You do have the option of unlocking everything, since there is no password, although this is not recommended.
  • Open: While the spreadsheets are not free of charge, they are "open software" in the sense that all their inner workings are exposed to the user.  You are not getting a "black box."  If you are so inclined you may verify for yourself the correctness and accuracy of every formula and every piece of data coded into the spreadsheets.  If you like them, tell others; if you find errors or have suggestions for improvements, please tell us.  All feedback is appreciated.
  • Portable: You may use the spreadsheets on any computing platform equipped with an Excel-compatible spreadsheet program or app.  (This is not included with your purchase here and is to be obtained separately from a third-party software provider.  Available choices include Microsoft Excel and OpenOffice Calc.)  We have successfully tested our spreadsheets on Macintosh, Windows, and Linux computers.  Excel spreadsheets can also be used on handheld devices; see available options for Windows Mobile, iPhone / iPod Touch / iPad, Blackberry, Palm, and also for Android.
  • Tested in "real life": See Customers' comments below.

 A one-page summary description of the suite can be downloaded here.

These pages provide a short manual describing the use of the spreadsheets.  We assume that you are already familiar with the principles and terminology of celestial navigation, and the use of sextants and almanacs.  In all spreadsheets the cells expecting the user’s input data are formatted in italics on green background and the results are displayed with the normal font in cyan cells, all next to labels in bold.  Cells marked yellow are used for both input and output (i.e. intermediate results). Except in the spreadsheet aries_stars.xls, the cells containing angular input data are formatted as compound fractions with three-digit denominators; thus the angle of 27° 31.1´ is to be entered as 27 311/600.  OpenOffice seems to have a problem here; you may need to reformat these cells using only two-digit fractions and therefore round angles to whole minutes.  Another alternative is to enter these values using the formula bar as: = 27 + 311/600.  If the value is negative (e.g. declination S 27° 31.1´), then in the formula you must use minus signs for both the whole degree component and its fraction: = -27 - 311/600.

These angular input data are accompanied by grey cells displaying the fractional portion of the data value in minutes of arc.  This way you can verify that the input value was entered correctly.  You may also use the minispreadsheet minutes.xls for the same purpose.  Enter the fractional value in cell B1, or the decimal value in cell B5, and inspect the equivalent angular value in arcminutes.

The formatting of some results does not explicitly separate the sign on output from the integer degree value.  Therefore, for results between -1° and +1° be sure to pick up the correct sign from the decimal value of the result in a neighboring cell, because zeroes are usually displayed unsigned.

Cells containing time data (with the exception of cell B6 in running_fix.xls, dr.xls, and dr_fix_lop.xls) are formatted using the 24-hour clock as HH:MM:SS.  (For times between 12 and 1 o’clock make sure that the cell ends up with the correct AM/PM value.)  All other cell contents represent partial results of the computations and can be ignored, unless you want to get into the nitty-gritty details.  Our adopted sign convention marks north latitudes and east longitudes as positive; south latitudes and west longitudes are considered negative.  On output, fractions of degrees (minutes of arc) are displayed without their sign.  We also provide a simple worksheet in which you may record intermediate results, such as output of almanac spreadsheets that need to be transferred into the input of the sight reduction spreadsheets.
If you have any questions or comments you may contact us at:    
pmh099@yahoo.com.


Happy navigating!




Alphabetical list of spreadsheets:

  1. alt_corr:    sextant altitude corrections
  2. aries_stars:    GHA of Aries and GPs of 57 main navigation stars
  3. dr_fix_lop:    estimated position from a DRP and a celestial LOP
  4. dr:    dead reckoning position (DRP)
  5. intercept:    intercept and azimuth for the St. Hilaire method
  6. jupiter:    almanac data for Jupiter
  7. lops:    two-body fix (using spatial geometry)
  8. lunar_distance:    LD clearing and chronometer resetting
  9. many_body_fix:    multiple LOP fix calculation
 10. mars:    almanac data for Mars
11. mercury:    almanac data for Mercury
12. minutes:    conversion of fractional angles into minutes of arc
13. moon:    almanac data for Moon (free download)
14. neptune:    almanac data for Neptune
15. noon_curve:    Sun LAN curve fix
16. noon_motion:    Sun LAN curve fix with motion of the vessel
17. noon_sight:    Sun LAN fix
18. one_body_fix:    fix from a zenith distance and azimuth
19. polaris_lha:    latitude from Polaris (LHA input)
20. polaris:    latitude from Polaris (UT input)
21. running_fix:    running fix (LOP1 advanced in time)
22. saturn:    almanac data for Saturn
23. sun:    almanac data for Sun
24. transit:    fix from a meridian transit on a moving vessel
25. two_body_fix: two-body fix (using spherical trigonometry)
26. uranus:    almanac data for Uranus
27. venus:    almanac data for Venus
Customers' comments:

Most yachts now have a laptop on board to receive weatherfax and similar.  My friend did several shots while crossing the Atlantic and got within 2nm, which is very creditable.  He and his crew did lots of shots because it is so easy - it just takes minutes, whereas, if you do it by a normal computer programme that requires you to look up the ephemeris, or, worse, if you are doing the sight reduction from tables, it takes much longer and there are many more opportunities for errors. 
 
The advantage of this presentation is that you put the minimum required information in the front page and the answer comes up immediately.  Because the cells and sheets are locked, you can't screw up the structure and because the user-input is minimal, the opportunity for error is dramatically reduced. 
 
If I use the spreadsheets to navigate my house (using an artificial horizon!) I frequently get within 0.25nm.  Obviously my garden (sorry, "yard") isn't heaving up and down so one expects a rather better result. 
Graham Cresswell, United Kingdom
 

I am new to CelNav and purchased these as soon as I learned they were available. As a former attorney, I hate wasting my time on mundane matters and sight reduction is mundane and so error prone! Use the spreadsheets and your navigation will improve. They are so inexpensive you are foolish not to try them. No, you may never use them about your boat, but the spreadsheets do discipline you and that is worth the price alone.
Richard Bash

Literature

  1. Jean Meeus, Astronomical Algorithms, Second Edition, Willmann-Bell (2005).
  2. Nautical Almanac, 2009 Commercial Edition, UK Hydrographic Office (2008).
  3. Nautical Almanac, 2010 Commercial Edition, UK Hydrographic Office (2009).
  4. The Astronomical Almanac for the year 2009, The Stationery Office, United Kingdom (2007).
  5. Explanatory Supplement to the Astronomical Almanac, Univ. Science Books (2006).
  6. The American Practical Navigator, (Bowditch 2002, 2004 revised PDF version).
  7. Thomas J. Cutler, Dutton's Nautical Navigation, 15th Edition, Naval Institute Press (2004).
  8. John Karl, Celestial Navigation in the GPS Age, Paradise Cay Publications (2007).
  9. David Burch, Emergency Navigation, Second Edition, McGraw-Hill (2008).
  10. David Owen Bell, The Celestial Navigation Mystery: Solved, Landfall Navigation (1999).
  11. Hewitt Schlereth, Celestial Navigation in a Nutshell, Sheridan House (2000).
  12. James A. Van Allen, An Analytical Solution Of The Two Star Sight Problem Of Celestial Navigation, Navigation 28 (1), (1981).
  13. http://eclipse.gsfc.nasa.gov/SEhelp/deltatpoly2004.html
  14. http://simbad.u-strasbg.fr/
  15. http://www.absoluteastronomy.com/stars/
Site launched: 19 February 2009
Last modified: 25 February 2010

Site visits:    
Copyright . C V Imaging. All rights reserved.