NavList:

   

Reply

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
--~--~---------~--~----~------------~-------~--~----~
Navigation List archive: www.fer3.com/arc
To post, email NavList@fer3.com
To , email NavList-@fer3.com
-~----------~----~----~----~------~----~------~--~---

File:

   

Reply