NavList:

   

Reply

A similar niche topic is time codes for video hh:mm:ss:ff where ff is
the frames.

 https://forum.openoffice.org/en/forum/viewtopic.php?t=78536

Some nice spreadsheet tricks here, including "MULTIPLE.OPERATIONS() to
avoid helper columns."  a feature I did not know existed. But I would
usually write a program rather than find ways to make a spreadsheet do
things that its Creator ( Bob Frankston) never intended!

Bill

On Wed, 9 Oct 2024 at 10:32, NavList Community  wrote:
>
> Re: Excel for sight reductions
> From: Frank Reed
> Date: 2024 Oct 9, 02:24 -0700
>
> Phil wrote:
> "A simple example of what I'd like to do is to take an altitude (say) 
62°30.2' and subtract a correction (say) 0.8' and get a result (say) 62°29.4' 
. This functionality seems to be available for time (at least as a HHMMSS) 
which is close but HH is limited to 24. Obviously I could go to Degrees and 
decimal degrees (DD) and just use as a decimal number, I just thought Excel 
might be smarter than that."
>
> Yes, I see what you're saying here. You were hoping for "native" support of 
sexagesimal numbering, complete with the 20th century navigator's preference 
for DDmm.m (degrees and decimal minutes). No. That doesn't exist.
>
> For everyone (not just Phil):
> Celestial navigation is a tiny backwater, barely used by anyone. You have to 
use the tools that exist, and they are not built to satisfy minor 
communities. We have to "roll our own". That's why so many solutions were 
offered here. Far larger communities have to deal with similar issues. For 
example "hex" numbers or "hexadecimal" are widely used in coding and 
hardware/software design. If I want to add a number like "C4" to "FF2A01FA", 
I have to use conversion functions to go to decimal and back. The functions 
are at least internal in Excel (and its open-source clones), but there's no 
true native support even for something as common as "hex". Maybe even more 
remarkable is that Excel and its cousins have no support for trigonometric 
functions with degrees (decimal or otherwise!) as arguments. After all these 
decades, why can't I use a formula like =SIN(45°) instead of 
=SIN(RADIANS(45)) ...? [see my PS below]
>
> The closest you can get in Excel to support for angles in degrees, minutes, 
and tenths is to create an "illusion" of built-in functionality by doing the 
conversion in and out of decimal "off-screen". You can either do this with 
VBA code (known for historical reasons as "macros"), or you can use cell 
formulas out in the "alphabet forest" (cells starting with AA, for example). 
This can be an effective solution if you need it, but it does have high 
"maintenance costs".
>
> I don't recommend any of this. We can do all of celestial navigation with 
very few exceptions directly in decimal, and that's exactly what I have been 
teaching in my "Modern Celestial Navigation" workshops for years. Exceptions? 
There are no decimal sextants, as far as I know, so any sight you take will 
necessarily be given in degrees and minutes. Thus you have to do one 
"decimalization" right at the top (=deg+min/60). And sometimes, but not 
necessarily, it'a nice to have output in degrees and minutes. For example, if 
you're displaying an altitude meant to be compared directly with a sextant 
reading, or maybe just as a final option for plotting on some charts. Other 
than that, do the whole thing, beginning to end, in decimal degrees.
>
> Frank Reed
>
> PS: Doing the conversion back and forth from degrees to radians just to use 
trig functions is a huge nuisance, and in many software languages and tools 
like spreadsheets, there's a built-in function pair with annoyingly long 
names, like the RADIANS(x) and DEGREES(x) functions in Excel. These yield 
relatively unreadable cell formulas and code, and they do nothing internally 
than multiply and divide by a constant. My preference of long-standing is to 
drop the conversion factor into cell A1... =180/PI(). That displays 57.29... 
etc. Then I rename that cell to "kk". This is a handy trick, and if you don't 
know how, figure it out!! Now I can get my trig functions by converting back 
and forth, degrees to radians and radians to degrees in the usual way: 
=SIN(B3/kk) is the same as =SIN(RADIANS(B3)) but easier to type, easier to 
read, few parens to match up, and yields cleaner cell formulas. Or in the 
opposite direction, if I want the inverse cosine of some ratio, I can go with 
the built-in function and write =DEGREES(ACOS(B3/C3)) or I can write 
=kk*ACOS(B3/C3). Oh, and if you want to get a little more exotic with your 
formulas, you could rename cell A1 as Ω --or some other unicode character-- 
instead of kk.
>
> 
>
>
>  

   

Reply