Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My dad's gotten into astrology, so he needs to do calculations for it - and most books use the Degrees Minutes Seconds nonmenclature - is there any way to set excel or open office calc to handle calculations in terms of DMS?

Edit: We're currently using excel 2007

Edit2: I suspect installing open office / libre office will be an option, and there may not be a MS Excel way of doing it - so i widening the question, Office 2007 is preferred but pretty much any common windows spreadsheet will do

Edit 3: Apparently using time as a workaround dosen't allow for multiplication- which is needed in this case

share|improve this question
    
edited to add the version –  Journeyman Geek May 11 '11 at 7:01
    
This doesn't look hopeful, social.technet.microsoft.com/Forums/en-US/excel/thread/… –  Tog May 11 '11 at 7:21
    
edited to add OOo or libre office as options, and that we need to be able to multiply the values in question –  Journeyman Geek May 11 '11 at 8:48

2 Answers 2

Out of the box Excel has no support for what you are asking. There might be plugins around that help with this, but instead I'd like to offer a little work-around:

You can format numbers as time to get Excel (and probably OOo and LibreOffice) to display and calculate values that as you need with the correct divisions, bases, etc.

Using the following custom format (Excel, OOo and LibreOffice will have equivalents but I don't have a copy to hand to test) will render them with the correct symbols as well. The [h] prevents the hour resetting to zero as it reaches 24 allowing arbitrarily large angles. If you'd like leading zero's then just double-up each letter.

[h]º m' s\"

These can be added together, etc, as necessary (they are just numbers underneath, after all). However, this will only work if the final result is positive; Excel will not will render negative times.

If you need to convert this value in to a decimal degrees value, you just need to multiply by 24, because a "day" in Excel time format has a value of 1 (so an hour is 1/24th, etc).

Just make sure the cell this formula goes in has normal formatting - not time:

=A1*24

Alternatively, you can also extract specific components from the time value by using the following:

=INT(A1*24)    returns degrees
=MINUTE(A1)    returns minutes
=SECOND(A1)    returns seconds
share|improve this answer
    
apparently i need them to multiply as well. –  Journeyman Geek May 11 '11 at 8:46
    
ah, that's because each value isn't really itself, but 1/24th of itself, so 12º 0' 0" is really being considered as 0.5 by Excel. Try multiplying the final result of a multiplication (of two degrees values) by 24 to correct for this. You shouldn't need to do this multiplying by a scaler. –  DMA57361 May 11 '11 at 8:50

There's no way to tell Excel to switch between radians and degrees when using functions like e.g.

=SIN(DEGREES)

so you would either have to do explicit conversions in each formula, e.g.

=SIN(RADIANS(DEGREES + MINUTES/60 + SECONDS/3600))

or you could create a custom formula in VBA like


Function SinDMS(Deg As Double, Min As Double, Sec As Double) As Double

    SinDMS = Sin((3.1415 * 2 * (Deg + Min / 60 + Sec / 3600)) / 360)

End Function

(But it will be quite slow if there are many calculations)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.