Decimalising RA and Dec Values

[Matt]

Hi guys. I'm stuck on a really annoying problem at the moment.
Basically, I've been tracking the positions of a number of asteroids
over the last 2 months and using a program to output the RA and Dec of
the asteroid relative to it's position to the other stars in the sky.
Now so far I've been inputting the RA and Dec's in the following format
into an Excel spreadsheet:

00 03 34.43

Now I'm in a bit of predicament because I need to start playing about
with this data, so I need it in decimal form (i.e. 0.00054°) (note
these are completely random values I've giving ). However, because
I've been using spaces to separate the degrees, arc minutes and arc
seconds, Excel will not read the values as actual numbers and just the
cells as a zero.

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

Kind Regards,

Matt

 

[Eric]

Hi guys. I'm stuck on a really annoying problem at the moment.
Basically, I've been tracking the positions of a number of asteroids
over the last 2 months and using a program to output the RA and Dec of
the asteroid relative to it's position to the other stars in the sky.
Now so far I've been inputting the RA and Dec's in the following format
into an Excel spreadsheet:

00 03 34.43

Now I'm in a bit of predicament because I need to start playing about
with this data, so I need it in decimal form (i.e. 0.00054°) (note
these are completely random values I've giving ). However, because
I've been using spaces to separate the degrees, arc minutes and arc
seconds, Excel will not read the values as actual numbers and just the
cells as a zero.

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

Kind Regards,

Matt

Use search and replace, if you have to, copy the column out to a text editor
like TextPad and do the proper search and replace there, then copy it and
paste it back into the spreadsheet. Or... create a formula to read the cell
as text and convert it to decimal degrees and use the formula as part of
other formulas you plan on using to retrieve this data, then, you can leave
the cells as hours/mins/secs etc if you like the view that way.
Eric

 

[OG]

Now so far I've been inputting the RA and Dec's in the following format
into an Excel spreadsheet:

00 03 34.43

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

================

Personally I would insert 6 blank columns after the RA & Dec column, then
use Text to Columns to break the data into 4 of them as
RAHours, RAMinutes, DecDegrees and DecMinutes

Then to get the decimal values
RAHours + RAMinutes/60
DecDegrees + DecMinutes/60

Perform calculations using the last two columns.

 

[canopus56]

following format into an Excel spreadsheet: 00 03 34.43
[Matt needs decimal formatted degrees]

Matt,

I have a series of VBA modules for Excel - two of which will translate
between text RA and Dec and decimal degrees. Module 1 contains two
routines; sample invocations are:

Module1.bas: Basic astrometry utilities.
http://members.csolutions.net/fisherka/astronote/plan/bas/Module1.bas

? Convert_RA2DecDeg("03 00 00","HH MM SS","double") yields 45
? Convert_DMS2DecDeg("+34 43 00","sDD MM SS","double") yields 34.716667

You'll need to use text functions (IF, MID, Left, Right, Concanetate) to
coax you input into a form recognized by the functions.

Convert_RA2DecDeg recognizes the forms:

"HHMMSS"
"HH MM SS"
"HH MM SS.SS"
"HHMMSS.SS"
"HH:MM:SS"
"HHh MMm SSs"

Convert_DMS2DecDeg recognizes the forms:

"sDDMMSS" ' Sign is optional
"sDD MM SS"
"sDD MMm SS.S"
"sDDd MMm SSs" ' This format includes sDD° MM' SS"
"sDDDMMSS"
"sDDD MM SS"
"sDDD MM SS.S"
"sDDDd MMm SSs"

A third function, Convert_ReformatTxtCoor(), reformats coordinate text
strings into a various text formats.

My amateur Excel observing spreadsheet project utilizes these functions:

Deep Sky Observing Planning Spreadsheet (July 2006)
http://members.csolutions.net/fisherka/astronote/plan/ObSpdht/ObSpdhtProject.html

The functions are all after Meuss or Duffett-Smith's books.

Clear skies.

- Canopus56

P.S. -

Other modules in the series are:

Module2.bas: Stellar magnitude utilities.
http://members.csolutions.net/fisherka/astronote/plan/bas/Module2.bas

Module3.bas: Astrophotography utilities.
http://members.csolutions.net/fisherka/astronote/plan/bas/Module3.bas

Module4.bas: Solar and lunar position.
http://members.csolutions.net/fisherka/astronote/plan/bas/Module4.bas

 

[Matt]

Firstly may I thank everyone for such great responses.

I've given the method using the Excel modules a go, but I think it is a
little beyond my ability with Excel at present. Basically I'm not sure
how to input the data into the module, so I just get the "#VALUE"
coming into the cell whenever I try to use it, even when I try copying
and pasting in your example as follows:

? Convert_RA2DecDeg("03 00 00","HH MM SS","double") yields 45

though it does complain about the question mark, so I'm sure I'm doing
something wrong. If you could give me a bit of an idiots guide
regarding how I input a piece of data into this module that would be
great.

The method of using the LEFT and RIGHT functions in Excel was also very
intriguing and works great for getting the degrees and arc seconds out
of a coordinate. However, I'm unable to get the arc minutes by itself
because it is in the middle (i.e. I either use LEFT (J2, 6) and get the
first 6 characters which includes the degrees bit as well, or RIGHT
(J2,6) and get the arc seconds bit). Is their something really obvious
I'm missing with using this function? Could their be a MIDDLE
function or something like that?

Kind Regards,

Matt

 

[Al]

The method of using the LEFT and RIGHT functions in Excel was also
very
intriguing and works great for getting the degrees and arc seconds
out
of a coordinate. However, I'm unable to get the arc minutes by
itself
because it is in the middle (i.e. I either use LEFT (J2, 6) and get
the
first 6 characters which includes the degrees bit as well, or RIGHT
(J2,6) and get the arc seconds bit). Is their something really
obvious
I'm missing with using this function? Could their be a MIDDLE
function or something like that?

Kind Regards,

Matt

Assuming J6 is one of a column of entries in a format like "00 03
34.43"
then cell you can have some cell (let's say K6) with entry
=LEFT(J6, 6) which gives you the six leftmost characters of J6. Why
six? If the degrees value is >99 then you need some way to ensure you
get all of the relevant characters. (It's worth noting that in cell J6
the "numbers" are not really numbers as far as Excel is concerned -
they are treated as text. Excel has ways around this). So now cell K6
either has (the degrees then a space then the minutes then a space) or
if the degrees value was greater than 99 then K6 has (degrees then a
space then the minutes). So far so good?

Then we use cell L6 to extract the degrees with
=VALUE(LEFT(K6,3))
working from the inside brackets out this formula takes the left 3
"numbers" then converts the text value to an integer (which is the
degrees value that you wanted). The neat bit is that Excel will
automatically filter out a trailing or leading space. Finally use cell
M6 to extract the minutes with
=VALUE(RIGHT(K6,3)

The overall concept is that to get to the middle you split the thing
in two; the middle is now one of the ends of the two new pieces...

 

[canopus56]

Matt wrote:
<snip all>

In a cell you would use:

=Convert_RA2DecDeg("03 00 00","HH MM SS","double")

I suggest you use a hybrid of "OG" and Greg's suggestions. When
dividing to make hours and minutes uniform to decimal degrees, remember
that a arcminute and arcsecond of right ascension and an arcminute and
arcsecond of declination do not have the same value.

For right ascension -

360 deg = 24 hours
15 deg = 1 hour
15 deg = 60 arcminutes
1 deg = 4 arcminutes
1/4 deg = 1 arcminute
1/4 deg = 60 arcseconds
1/4 * 1/60 deg = 1 arcsecond
0.004167 deg = 1 arcsecond

There are 15 degrees in an hour of right ascension (360 degrees / 24
hours ra).

There are 0.25 degrees in an arcminute of right ascension ( ( 15
degrees / 1 hour ra) / ( 60 arcmins / 1 hour ra) = 0.25 degrees)

There are 0.004167 degrees in an arcsecond of right ascension ( ( 15
degrees / 1 hour ra) / ( 3600 arcseconds / 1 hour ra) = 0.004167
degrees)

So to convert from ra to decimal degrees, you might use something like:


= (iHours * 15) + (iMin * 0.25) + (dSec * 0.004167)

or

= (iHours * 15) + (iMin * 0.25) + ( ( dSec * 15) / 3600 )

In declination, the values are:

360 deg = 360 degrees
1 deg = 1 deg
1 deg = 60 arcminutes
1/15 deg = 1 arcminute
1 arcminute = 60 arcseconds
1/15 deg = 60 arcseconds
1 ( 15 * 60 ) = 1 arcsecond
0.000278 deg = 1 arcsecond

Verses in declination, there are 0.01667 degrees in an arcminute of
declination ( 1 deg / 60 arcminutes = 0.01667 degrees).

Verses in declination, there are 0.000278 degrees in an arcsecond of
declination ( 1 deg / 3600 arcseconds = 0.000278 degrees).

To convert declination to decimal degrees you might use something like:


= Round(((iDeg + (iMin / 60) + (dSec / 3600)) * iSign), 6)

The Excel function to extract the middle of a string is "MID"; to
convert the string to a value is (Value), e.g. -

=MID("00 03 00",4,2) yields "03"
=Value(MID("00 03 00",4,2)) yields 3.0

Hope that helps.

- Canopus56

P.S. -

The main books on astronomical algorithms are:

Duffet-Smith1988: Duffet-Smith, P. 1988 (3ed). Practical Astronomy with
Your Calculator. Cambridge Press.
http://www.willbell.com/
http://adsabs.harvard.edu/cgi-bin/nph-bib_query?bibcode=1988QB62.5.D83.....

Meeus, J. 1998. 2ed. Astonomical Algorithms. Willmann-Bell. ISBN
0-943396-61-
http://www.willbell.com/

Montenbruck, Oliver. 1989. Practical ephemeris calculations.
Springer-Verlag.

Duffet-Smith can usually be found in local libraries; Meeus and
Montenbruck at university libraries.

 

[Odysseus]

Now so far I've been inputting the RA and Dec's in the following format
into an Excel spreadsheet:

00 03 34.43

Does anyone know of anyway that I could somehow get Excel to insert
colons into the two spaces, or get it to recognise numbers written in
this format?

================

Personally I would insert 6 blank columns after the RA & Dec column, then
use Text to Columns to break the data into 4 of them as
RAHours, RAMinutes, DecDegrees and DecMinutes

Then to get the decimal values
RAHours + RAMinutes/60
DecDegrees + DecMinutes/60

Perform calculations using the last two columns.

I've used a similar approach as well, when I didn't want to bother with
string manipulations. Where I have both decimal and sexagesimal values
in a CVS file, before importing into Calc (which is very similar to
Excel -- this Mac is a largely Microsoft-free computer) I insert two
tabs after the former and separate the D (or H), M, & S of the latter
with tabs. That way the same formulae can be used for both types of
data, as the blank M & S fields will be treated as zeroes.

 

[dylan]

360 deg = 360 degrees
1 deg = 1 deg
1 deg = 60 arcminutes
1/15 deg = 1 arcminute
1 arcminute = 60 arcseconds
1/15 deg = 60 arcseconds
1 ( 15 * 60 ) = 1 arcsecond
0.000278 deg = 1 arcsecond

Are you sure on this ?

1 deg = 60 arcminutes
1/15 deg = 1 arcminute

surely...
1 deg = 60 arcminutes
1/60 deg = 1 arcminute ?

 

[canopus56]

dylan wrote:

1 deg = 60 arcminutes
1/15 deg = 1 arcminute

surely...
1 deg = 60 arcminutes
1/60 deg = 1 arcminute ?

Yeah, that's for right ascension, not declination. A minute in right
ascension is not the same size as a minute in declination. Confusing,
ain't it?

1/60 deg = 1 arcminute ?

Is true for declination only.

- Canopus56

 

[Brian Tung]

Yeah, that's for right ascension, not declination. A minute in right
ascension is not the same size as a minute in declination. Confusing,
ain't it?

Yes, but the term "arcminute" usually implies that 1/60 of a degree is
meant. If what is meant is 1/60 of an hour of RA, then the usual term
is "minute of RA," not "arcminute." Also, since an hour of RA is 15
degrees, a minute of RA is 1/4 of a degree, not 1/15.

--
Brian Tung <[email protected]>
The Astronomy Corner at http://astro.isi.edu/
Unofficial C5+ Home Page at http://astro.isi.edu/c5plus/
The PleiadAtlas Home Page at http://astro.isi.edu/pleiadatlas/
My Own Personal FAQ (SAA) at http://astro.isi.edu/reference/faq.html

 

[canopus56]

Are you sure on this ?
1 deg = 60 arcminutes
1/15 deg = 1 arcminute
surely...
1 deg = 60 arcminutes
1/60 deg = 1 arcminute ?

My apologies. Your're right. To correct, the declination list should
have read:

In declination, the values are:

360 deg = 360 degrees
1 deg = 1 deg
1 deg = 60 arcminutes
1/60 deg = 1 arcminute
1 arcminute = 60 arcseconds
1/60 deg = 60 arcseconds
1 / ( 60 * 60 ) = 1 arcsecond
1 / (3600) = 1 arcsecond
0.000278 deg = 1 arcsecond

- Canopus56

 

[burtond]

DO NOT TRY TO USE EXCEL FOR ASTRONOMY

I did, the problem is that southerly declinations are written as minus
(degrees minutes and seconds). For between 0 and 1 degree south you
will need -00 degrees as a seperate value to +00 degrees. Excel
cannot do this, -0 is always converted to +0.

I found out with the locations of Globular clusters, I think it was
POL4 that is in the critical area.

Try Perl instead of EXCEL.

Dave Burton

 

[Chris L Peterson]

DO NOT TRY TO USE EXCEL FOR ASTRONOMY

I did, the problem is that southerly declinations are written as minus
(degrees minutes and seconds). For between 0 and 1 degree south you
will need -00 degrees as a seperate value to +00 degrees. Excel
cannot do this, -0 is always converted to +0.

I do it all the time. You just put the sign in its own column- no
biggie, since it's easiest to have the dms values in their own columns,
too.

However, if the dms values are entered in a single column, they are
treated as a string, and again there is no problem with the sign- it
will be preserved, even in the case of '-00 00 01'. When the values are
in a single column, you use a string function (like left()) to determine
the sign, and just multiply that by the decimalized value.

Nothing against Perl, but there are times when a spreadsheet is much
better tool than a programming language!

_________________________________________________

Chris L Peterson
Cloudbait Observatory
http://www.cloudbait.com

 

[Paul Schlyter]

Yes, but the term "arcminute" usually implies that 1/60 of a degree is
meant. If what is meant is 1/60 of an hour of RA, then the usual term
is "minute of RA," not "arcminute." Also, since an hour of RA is 15
degrees, a minute of RA is 1/4 of a degree, not 1/15.

1/15 hour = 1 degree
1/15 min = 1 arcminute
1/15 sec = 1 arcsecond

That's the way it should be!

I usually call a minute of RA a "minute of time". After all, 60
minutes of RA becomes one hour of RA, and an hour is a time unit
really. If you wait one sidereal hour, then the RA of your local
central meridian will increase by exactly one hour. If you wait one
sidereal minute, then the RA of your local central meridian will
increase by exactly one minute (of time, or of RA) ... etc. So the
angular units of RA (hr, min, sec) is really angularized time unit,
with the sidereal rotation rate of the Earth as the conversion factor.

One arcminute and one arcsecond are always 1/60 resp 1/3600 of a
degree, not of an hour! That's what the "arc" means: we're measuring
angles, not time. And RA hours, minutes, seconds are really time
units, although in disguise as angular units.