Interactive Spreadsheets for Celestial Navigation
Introduction



Welcome to the Navigation SpreadsheetsSM website!

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).

Celestial navigation continues to be of practical importance today. Slides from a recent (April 2013) presentation by the United Kingdom Hydrographic Office about the future of celestial navigation are available here.

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:


Click here to download a single demonstration spreadsheet (calculation of almanac data for the Moon).


All fourty-seven spreadsheets plus the PDF version of the manual with their detailed descriptions are now available for free download:

DOWNLOAD TAR ARCHIVE FILE (13 MB)

DOWNLOAD ZIP ARCHIVE FILE (7 MB)


Downloads of individual uncompressed files are available at the alphabetical list below.




A separate product, T-Plotter®, that facilitates simplified plotting of celestial LOPs (lines of position) can be also be obtained, for a fee, via this site.


The name of the payment recipient on all transactions is "C V Imaging".




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.4'.  The polaris.xls and polaris_lha.xls spreadsheets are 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 algorithms used in our almanac spreadsheets have a large range of validity both into the future and the past.  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: that is, free of charge, to be more specific.
  • 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: The spreadsheets 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 two-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.  Finally, angles (in degrees) can also be entered in decimal form (27° 31.1´ = 27.5183).

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; hour angles increase in the westward direction.  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.


You can also follow us on our blog, which was ported to a new host in August 2014.

Happy navigating!






Alphabetical list of spreadsheets with individual downloads: 

  1. alt_corr:    sextant altitude corrections  (DOWNLOAD)

  2. alt_move:   altitude corrected for motion of the vessel  (DOWNLOAD)

  3. alt_prec:   precomputed sextant altitude  (DOWNLOAD)

  4. amplitude:    amplitude and azimuth of a rising or a setting body  (DOWNLOAD)

  5. aries_stars:    GHA of Aries and GPs of 57 main navigation stars  (DOWNLOAD)

  6. average1:   averaging of sights (precomputed slope)  (DOWNLOAD)

  7. average2:   averaging of sights (fitted slope)  (DOWNLOAD)

  8. composite:   composite sailing calculation  (DOWNLOAD)

  9. course_and_speed:    vessel's speed to counteract the set and drift  (DOWNLOAD)

10. course_to_steer:    vessel's heading for a given ground track  (DOWNLOAD)

11. cpa: closest point of approach from two ranges and relative bearings  (DOWNLOAD)

12. dip_short:    dip short of the horizon  (DOWNLOAD)

13. distance:    distance by vertical angle  (DOWNLOAD)

14. dr:    dead reckoning position (DRP)  (DOWNLOAD)

15. dr_fix_lop:    estimated position (EP) from a DRP and a celestial LOP  (DOWNLOAD)

16. ex_meridian:    ex-meridian latitude calculation (time away from transit input)  (DOWNLOAD)

17. ex_meridian_t:    ex-meridian latitude calculation (meridian angle input(DOWNLOAD)

18. ground_speed:    from vessel speed and set and drift  (DOWNLOAD)

19. intercept:    intercept and azimuth for the St. Hilaire method  (DOWNLOAD)

20. jupiter:    almanac data for Jupiter  (DOWNLOAD)

21. ld_prec: geocentric and topocentric lunar distance from almanac data  (DOWNLOAD)

22. lops:    two-body fix (using spatial geometry)  (DOWNLOAD)

23. lunar_distance:    LD clearing and chronometer resetting  (DOWNLOAD)

24
. many_body_fix:    multiple LOP fix calculation  (DOWNLOAD)

25. mars:    almanac data for Mars  (DOWNLOAD)

26. mercury:    almanac data for Mercury  (DOWNLOAD)

27. minutes:    conversion of fractional angles into minutes of arc  (DOWNLOAD)

28. moon:    almanac data for Moon  (DOWNLOAD)

29. neptune:    almanac data for Neptune  (DOWNLOAD)

30. noon_curve:    Sun LAN curve fix  (DOWNLOAD)

31. noon_motion:    Sun LAN curve fix with motion of the vessel  (DOWNLOAD)

32. noon_sight:    Sun LAN fix  (DOWNLOAD)

33. one_body_fix:    fix from a zenith distance and azimuth  (DOWNLOAD)

34. polaris:    latitude from Polaris (UT input)  (DOWNLOAD)

35. polaris_lha:    latitude from Polaris (LHA input)  (DOWNLOAD)

36. running_fix:    running fix (LOP1 advanced in time)  (DOWNLOAD)

37. sailings:    great-circle and rhumb-line sailings  (DOWNLOAD)

38. saturn:    almanac data for Saturn  (DOWNLOAD)

39. set_and_drift:    set and drift from difference between DRP and EP  (DOWNLOAD)

40. sun:    almanac data for Sun  (DOWNLOAD)

41. time:    conversion of time data between formats  (DOWNLOAD)

42. transit:    fix from a meridian transit on a moving vessel  (DOWNLOAD)

43. two_body_fix: two-body fix (using spherical trigonometry)  (DOWNLOAD)

44. uranus:    almanac data for Uranus  (DOWNLOAD)

45. venus:    almanac data for Venus  (DOWNLOAD)

46. waypoints:    rhumb-line sailing between great-circle waypoints  (DOWNLOAD)

47. what_star:    star identification based on altitude and azimuth  (DOWNLOAD)


Download manual.pdf

Download worksheet.pdf


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

Site visits:    
Web Hosting Companies