2008/6/14 mike scott <[EMAIL PROTECTED]>: > On 14 Jun 2008 at 9:37, Harold Fuchs wrote: > > > 2008/6/14 JOE Conner <[EMAIL PROTECTED]>: > > > > > In Calc I would like to format a cell containing a number such as > 189.25 to > > > display such as 180 15. > > > This can represent 189 and 1/4 degrees displayed as 189 minutes 15 > seconds. > > > Such a formatting condition will be useful in displaying solutions to > > > navigation problems. > > > > > > I have tried tinkering with the user format cell functions - no joy. > Does > > > anyone have a suggestion? > > > > > > Thank you. > > > Joe Conner, Poulsbo, WA USA > > > > > > > The best I can come up with is as follows: > > Assume the 189.25 is in A1. > > 1. In B1 put the formula "=a1/24" > > 2. Format B1 using the user defined format "[HH] MM" > > > > I found it by fiddling with time formats but I'm not 100% sure why it > works > > :-( Perhaps someone would be kind enough to explain - please! > > Well, the time format stuff assumes the input is days. So if you > pretend his degrees are hours, divide by 24 to get "days", then the > HH format will convert the "days" back to hours, and then the minutes > and seconds follow. The help file is singularly unhelpful - as is > the manual - about the square brackets. AFAICS a plain HH will wrap > back to 0 at 24 hours - [HH] seems to run past 24. Or maybe everyone > but me knew this anyway :-)
Not everyone; I certainly didn't :-( > > (I assume, btw, the op wants to display "189 and 1/4 degrees" > displayed as "189 degrees 15 minutes" though, and not as stated!! ) You can include text in the format string. So, for example (using <> as quotes for the purposes *only* of this message) <[HH]\° MM\"> gives <189° 15"> and <[HH] "degrees" MM "minutes"> gives <189 degrees 15 minutes>. The backslashes merely "escape" the degree sign and the double quote (minute) sign. > > But it's a nasty hack. The number format stuff should be flexible to > allow this formatting directly. Motion seconded. I'm surprised there is no formatting for degrees/minutes/seconds. -- Harold Fuchs London, England Please reply *only* to [email protected]
