Welcome to the NavList Message Boards.


A Community Devoted to the Preservation and Practice of Celestial Navigation and Other Methods of Traditional Wayfinding

Compose Your Message

Add Images & Files
    Re: Navigation spreadsheet lessons
    From: Frank Reed
    Date: 2009 Jan 15, 19:36 -0800

    Let's see if we can make a dip table that matches the values in the Nautical Almanac using a spreadsheet.
    We can get the exact equation for geometric dip from simple geometry. It is 
    dip = arccos[RE/(RE+h)] where RE is the radius of the Earth, h is height of 
    eye, and the result is in radians so we have to convert to minutes of arc to 
    compare with the table. There's also an approximate equation for the 
    geometric dip which we can derive in the small angle limit. It's dip = 
    Continuing in the same spreadsheet, in cell A15 let's put in a label: "R 
    earth:". Then in B15, put 3960 which is the radius of the Earth in statutes 
    miles, near enough. In C15, enter the formula =5280*B15.
    Skip down a few lines and let's add some labels for the table. Put "height 
    (ft)", "geometric dip", "g. dip approx.", and "corrected dip" in cells B18 
    through E18. Then put some values for height in feet under the label. I 
    selected a few special cases: 2,4,6,8,10,70,80,90. Put these in the cells 
    directly beneath B18. Now for the formulas.  Go to cell C2, and type in 
    =60*degrees(acos(c15/(c15+b19))). Look carefully at this formula and you 
    should be able to see that it reproduces the exact equation for geometric 
    dip. But wait... In C15 is the radius of the Earth (in feet), but cell 
    references are relative so if we use this formula and then copy it down, it 
    will no longer refer to the radius of the Earth. In this case we need an 
    absolute reference. And this brings us to another important bit of 
    "spreadsheet-eze". Absolute cell references are created by using "dollar 
    signs" in the formulas. So you type $c$15 to refer to that cell in such a way 
    that it will always point to that cell, even if the formula is copied or 
    moved. And there's a little trick, dating back to the earliest spreadsheets, 
    that lets you cycle through the various referencing options: the F4 key on 
    your keyboard will convert a relative reference to an absolute one. Another 
    trick found in most spreadsheets. To edit a formula without having to re-type 
    it all, hit the F2 key.
    We have our first formula for dip ready to go. It is now 
    =60*degrees(acos($c$15/($c$15+b19))). Copy that formula and then paste it into 
    cells C20 through C26. And there's our first little dip table. The dip 
    formula for the (slightly) approximate geometric dip is 
    =60*degrees(sqrt(2*b19/$c$15)). Enter that in cell D19. Copy it and past into 
    cells D20 to D26. It's immediately obvious that the slightly approximate 
    equation is sufficiently accurate by a long way. And yet, if you check 
    against the almanac table, we're not really close with either equation.
    We need one more formula to get near the almanac table. Refraction modifies 
    dip by some factor. So let's add a label below "R earth:". In cell A16, type 
    "ref factor:". Then in cell B16, enter 0.85. Next below the label that says 
    "corrected dip" enter this simple formula =$b$16*d19. Copy and paste to fill 
    out that column. 
    At this point it might be nice to clean up the table a little. We don't need 
    all those digits after the decimal point. Go up to the tool bar and you will 
    see a tool that is labeled "123". When you float your mouse over it, it says 
    "More Formats". Mark out (select with your mouse) all the numbers in the 
    table, go to the "123" tool and pick the one labeled "2 decimals". That 
    should look better.
    And now we can finally do something useful with this spreadsheet. You can 
    enter trial values for "ref factor" until your table matches the one in the 
    Nautical Almanac. And just that easily you have reverse-engineered their 
    PS: short on time again so I have not had a chance to proof-read this. Image 
    attached of this section of the spreadsheet
    Navigation List archive: www.fer3.com/arc
    To post, email NavList@fer3.com
    To unsubscribe, email NavList-unsubscribe@fer3.com


    Browse Files

    Drop Files


    What is NavList?

    Join NavList

    (please, no nicknames or handles)
    Do you want to receive all group messages by email?
    Yes No

    You can also join by posting. Your first on-topic post automatically makes you a member.

    Posting Code

    Enter the email address associated with your NavList messages. Your posting code will be emailed to you immediately.

    Email Settings

    Posting Code:

    Custom Index

    Start date: (yyyymm dd)
    End date: (yyyymm dd)

    Visit this site
    Visit this site
    Visit this site
    Visit this site
    Visit this site
    Visit this site