Welcome to the NavList Message Boards.

NavList:

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

Compose Your Message

Message:αβγ
Message:abc
Add Images & Files
    or...
       
    Reply
    Re: ExcelNav.ZIP Question
    From: UNK
    Date: 2006 Apr 18, 11:25 -0400

    On Saturday, April 08, 2006 10:20 AM, Robert Gainer said:
    > Not being a person that can program an Excel spreadsheet, I am not even
    > sure how to ask the question. In my copy of Excel, using the dialog box
    > for formatting cells I see you can define an input field as "custom" and
    > have input in combinations of time and number. That is to say, enter a
    > number + minuets, and seconds (##0 MM SS) or just HHH MM SS using that
    > custom mask. Why can't the spreadsheet you wrote use time as a stand-in
    > for degrees, minuets and seconds of an angle. The spreadsheet understands
    > that a minuet and a second have 60 integers in them so it would seem like
    > you can trick it into using that input mask to make a user friendly front
    > end. If I understand it correctly, you can unstring the data to use in
    > calculations later in the spreadsheet so it looks doable in the company
    > of a sufficient amount of ignorance on my part.
    
    Interesting idea, but I'm not sure it really simplifies the sheets.
    
    As they stand, the sheets are built to accept angles as decimal degrees.
    Thus, 12? 34.5' is entered as +12+34.5/60. An adjacent cell displays this
    formatted as degrees, minutes and tenths for clarity. Calculations are
    carried out on the decimal degrees; trig functions are wrapped in the
    DEGREES() and RADIANS() functions to satisfy their appetite for radians.
    This makes the spreadsheets look similar to paper work forms (and easier to
    debug). This is actually why I wrote these spreadsheets to replace the C
    programs (derived from earlier Fortran programs) that I had used for years:
    to have a visible, step-by-step solution rather than a black box that spat
    out answers.
    
    Using Excel's native HH:MM:SS format has two problems.
    
    First, the resulting numbers, which Excel thinks are time, are stored as
    fractions. Excel's date-time format encodes dates as days since 1-Jan-1900
    and time of day as fractional days. Having angles expressed as fractions
    [0,1] is neither as intuitive as decimal degrees [0,360], nor as
    computationally convenient as radians [0,2pi].
    
    Second, there's a gotcha in the way Excel interprets time entries. 12:34:56
    is unambiguously interpreted as hours:minutes:seconds and resolves to
    0.524259 (12/24 + 34/1440 + 56/86400). Likewise, 12:34 is interpreted as
    hours:minutes and resolves to 0.523611 (12/24 + 34/1440). BUT 12:34.5, which
    to a navigator would imply 12? 34.5', is interpreted by Excel as
    minutes:seconds and resolves to 0.008733 (12/1400 + 34.5/86400). Thus
    XX:YY[.Z] is interpreted differently depending on whether a fraction is
    present. One could probably kluge around this, but you'd be steering into
    danger.
    
     -- Peter
    
    -----Original Message-----
    From: Navigation Mailing List [mailto:NAVIGATION-L{at}LISTSERV.WEBKAHUNA.COM]
    On Behalf Of Robert Gainer
    Sent: Saturday, April 08, 2006 10:20 AM
    To: NAVIGATION-L{at}LISTSERV.WEBKAHUNA.COM
    Subject: ExcelNav.ZIP Question
    
    Peter, and others
    Not being a person that can program an Excel spreadsheet, I am not even sure
    how to ask the question. In my copy of Excel, using the dialog box for
    formatting cells I see you can define an input field as "custom" and have
    input in combinations of time and number. That is to say, enter a number +
    minuets, and seconds (##0 MM SS) or just HHH MM SS using that custom mask.
    Why can't the spreadsheet you wrote use time as a stand-in for degrees,
    minuets and seconds of an angle. The spreadsheet understands that a minuet
    and a second have 60 integers in them so it would seem like you can trick it
    into using that input mask to make a user friendly front end. If I
    understand it correctly, you can unstring the data to use in calculations
    later in the spreadsheet so it looks doable in the company of a sufficient
    amount of ignorance on my part.
    Thanks,
    Robert Gainer
    
    
    

       
    Reply
    Browse Files

    Drop Files

    NavList

    What is NavList?

    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)

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