NavList:

   

Reply

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@LISTSERV.WEBKAHUNA.COM]
On Behalf Of Robert Gainer
Sent: Saturday, April 08, 2006 10:20 AM
To: NAVIGATION-L@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