# NavList:

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

**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