Degree minutes seconds

[RDK]

Excel has trouble using degree minutes and seconds. As we know the trig functions use radian measure.

Anyone ever run across a macro that would convert between dd.mmss to radian and allow formating in dd.mmss?

Rick Kitson MBA P.Eng

Construction Project Management
From conception to completion

http://www.kitsonengineering.com

 

[prex]

A trick may be that you use the format for times as [h].mm.ss: this interprets the number that's in the cell as a number of days and transforms it in total hours followed by minutes and seconds of time that are just like the minutes and seconds of an angle.
If this is not too complicated for you these would be the steps:
1)you have a cell with an angle in radians (that is perhaps the result of an inverse trigonometric function)
2)in another cell convert this to degrees (*180/pi)
3)in another cell convert the result 2 by dividing it by 24 (this can be done also in a single step under 2)
4)to the last cell apply the custom format as above. prex
motori@xcalcsREMOVE.com

http://www.xcalcs.com

Online tools for structural design

 

[RDK]

That will work with the built in format of elasped hh.mmss but what I was looking for was someway of entering and using numbers in degrees. minutes and seconds rather than radian.

What i had been doing was using three columns, applying a custom format to have the proper symbols and then in a hidden colum converting them to decimal gegrees and then radians.

I would like to be able to add and subtract angles using dd.mm.ss formats and do other calculations with the angles.


Rick Kitson MBA P.Eng

Construction Project Management
From conception to completion

http://www.kitsonengineering.com

 

[prex]

I can't see a straight through way of doing what you want. In Excel you cannot do directly numerical operations on anything that is not a number (dates are numbers, currency values are numbers), but you can show those numbers in a number of ways by means of predifined or custom formats. You can even enter those numbers using the format, for example a time by writing 1.25.58 (that will be internally transformed to the number 0.05831), but you can't edit them using the same format, as, as far as I know, the format of the formula bar cannot be changed.
Another way to attack the problem is that you enter your ddmmss as strings (just write in a cell something like 56°25'33'' and Excel will consider it as a string), then write a VB function (e.g. [tt]dms2rad(dms as string)[/tt]) that interprets it and returns the corresponding number in radians (this is not very difficult to write down). At that point you will enter in a separate cell the formula [tt]=dms2rad(ref)[/tt] and do all subsequent calculations on that result.
This is not really very different from what you already do, but it's the best I can think of... prex
motori@xcalcsREMOVE.com

http://www.xcalcs.com

Online tools for structural design

 

[Kryten]

if you enter the angle/bearing as 56:25:33 then Excel treats this as HH:MM:SS. if you multiply this by 24, and change the formatting to decimals, you will have decimal degrees. then simply use the =radians() function to convert to radians.

 

[stick1]

RDK,
Check out the "How to work with angles (degrees, minutes, and seconds)" post on 8/05/02 for a lengthy discussion of this topic.

 

[athyrio]

There is an add-in for excel which may be of help. Check out this web page.

http://nmml.afsc.noaa.gov/Software/ExcelGeoFunctions/excelgeofunc.htm

 

[furley]

i have some code for a custom function that goes from HMS to decimal and vice versa...email me at dfiler@hntb.com and i can send it to ya