Interactive Spreadsheets for Celestial Navigation
Interactive Spreadsheets for Celestial Navigation
Embedded Files
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.
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 commonly acquired in celestial navigation:
Intersections of lines of position
Running fix
Noon sight, noon curve, and meridian transits
Dead reckoning (DR) position and a DR fix along a line of position
Sextant altitude corrections
Solutions of the navigation triangle (the intercept method and sailings calculations)
Almanac data (calculation of Geographical Positions of main celestial bodies)
Lunar distance (UT recovery)
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. 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. 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. 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. 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.
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.
© Navigation Spreadsheets - All rights reserved.
SPREADSHEETS EXPLANATION
Lines of position (two-body) fix
Lines of position (many-body) fix
Meridian transit on a moving vessel
Ex-meridian latitude calculation
Dead reckoning fix of Estimated Position along LOP
Averaging of sights: 1. Precomputed slope
Averaging of sights: 2. Fitted slope
Altitude correction for motion of the vessel
Sight reduction using the intercept method
Great-circle and rhumb-line sailings
Lunar distance clearing and UT recovery
SPREADSHEETS DOWNLOADS
Direct downloads from this server:
ALPHABETICAL LIST OF SPREADSHEETS
01. alt_corr: sextant altitude corrections
02. alt_move: correction of observed altitude for motion of the vessel
03. alt_prec: precomputed sextant altitude
04. amplitude: amplitude and azimuth of a rising or a setting body
05. aries_stars: GHA of Aries and GPs of 57 main navigation stars
06. average1: averaging of sights (precomputed slope)
07. average2: averaging of sights (fitted slope)
08. composite: composite sailing calculation
09. course_and_speed: ground speed from the vessel speed and speed of current
10. course_to_steer: vessel course from set and drift and desired ground track
11. cpa: closest point of approach from two ranges and relative bearings
12. dip_short: dip short of the horizon
13. distance: distance by vertical angle
14. dr: dead reckoning position (DRP)
15. dr_fix_lop: estimated position (EP) from a DRP and a celestial LOP
16. ex_meridian: ex-meridian latitude calculation (time away from transit input)
17. ex_meridian_t: ex-meridian latitude calculation (meridian angle input)
18. ground_speed: ground speed from vessel speed and set and drift
19. intercept: intercept and azimuth for the St. Hilaire method
20. jupiter: almanac data for Jupiter
21. ld_prec: geocentric and topocentric lunar distance from almanac data
22. lops: two-body fix (using spatial geometry)
23. lunar_distance: LD clearing and chronometer resetting
24. many_body_fix: multiple LOP fix calculation
25. mars: almanac data for Mars
26. mercury: almanac data for Mercury
27. minutes: conversion of fractional angles into minutes of arc
28. moon: almanac data for Moon
29. neptune: almanac data for Neptune
30. noon_curve: Sun LAN curve fix
31. noon_motion: Sun LAN curve fix with motion of the vessel
32. noon_sight: Sun LAN fix
33. one_body_fix: fix from a zenith distance and azimuth
34. polaris: latitude from Polaris (UT input)
35. polaris_lha: latitude from Polaris (LHA input)
36. running_fix: running fix (LOP1 advanced in time)
37. sailings: great-circle and rhumb-line sailings
38. saturn: almanac data for Saturn
39. set_and_drift: set and drift from the difference between DRP and EP
40. sun: almanac data for Sun
41. time: conversion of time data between formats
42. transit: fix from a meridian transit on a moving vessel
43. two_body_fix: two-body fix (using spherical trigonometry)
44. uranus: almanac data for Uranus
45. venus: almanac data for Venus
46. waypoints: rhumb-line sailing between great-circle waypoints
47. what_star: star identification based on altitude and azimuth
User's Manual in PDF format
Worksheet in PDF format
Google Sites
Report abuse