# NavList:

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

Message:αβγ
Message:abc
 Add Images & Files Posting Code: Name: Email:
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 =
sqrt(2*h/RE).

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

-FER
PS: short on time again so I have not had a chance to proof-read this. Image
attached of this section of the spreadsheet
--~--~---------~--~----~------------~-------~--~----~
To post, email NavList@fer3.com
To unsubscribe, email NavList-unsubscribe@fer3.com
-~----------~----~----~----~------~----~------~--~---

```

File:

Browse Files

Drop Files

### Join NavList

 Name: (please, no nicknames or handles) Email:
 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:

### Email Settings

 Posting Code:

### Custom Index

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