| |
Main
Date: 23 Nov 2006 15:29:28
From: Matt
Subject: Decimalising RA and Dec Values
|
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=B0) (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
|
|
| |
Date: 23 Nov 2006 21:54:54
From: canopus56
Subject: Re: Decimalising RA and Dec Values
|
"Matt" <mattb95@hotmail.com > wrote in message
news:1164324568.694649.290330@h54g2000cwb.googlegroups.com...
<snip > Now so far I've been inputting the RA and Dec's in the
> 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
|
| |
Date: 24 Nov 2006 14:12:08
From: Al
Subject: Re: Decimalising RA and Dec Values
|
You can do this using the "right" and "left" functions in excel.
The basic concept is
=left( cell-reference, number of characters) or
=right( cell-ref, number of characters)
A couple of lines of cell formulae will have it sorted. Have a go
yourself or post back and I'll send you a file with ideas.
Al
"Matt" <mattb95@hotmail.com > wrote in message
news:1164324568.694649.290330@h54g2000cwb.googlegroups.com...
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
|
| | |
Date: 24 Nov 2006 07:50:51
From: Greg Neill
Subject: Re: Decimalising RA and Dec Values
|
> "Matt" <mattb95@hotmail.com> wrote in message
> news:1164324568.694649.290330@h54g2000cwb.googlegroups.com...
> 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?
Have you considered breaking the values out into
their own columns? (You can resize the columns
to preserve the look for printout).
One way to do this is to select the column of
cells that contain the "compressed" formatted
text, then on the tool bar select
Data -- > text to columns
and follow the "wizard".
|
| |
Date: 24 Nov 2006 00:19:09
From: OG
Subject: Re: Decimalising RA and Dec Values
|
"Matt" <mattb95@hotmail.com > wrote in message
news:1164324568.694649.290330@h54g2000cwb.googlegroups.com...
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.
|
| | |
Date: 25 Nov 2006 12:26:45
From: Odysseus
Subject: Re: Decimalising RA and Dec Values
|
In article <4sms45F10hphiU1@mid.individual.net >,
"OG" <owen@gwynnefamily.org.uk > wrote:
> "Matt" <mattb95@hotmail.com> wrote in message
> news:1164324568.694649.290330@h54g2000cwb.googlegroups.com...
>
> 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.
--
Odysseus
|
| | | |
Date: 26 Nov 2006 15:08:23
From: burtond
Subject: Re: Decimalising RA and Dec Values
|
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
On Sat, 25 Nov 2006 12:26:45 GMT, Odysseus
<odysseus1479-at@yahoo-dot.com > wrote:
>In article <4sms45F10hphiU1@mid.individual.net>,
> "OG" <owen@gwynnefamily.org.uk> wrote:
>
>> "Matt" <mattb95@hotmail.com> wrote in message
>> news:1164324568.694649.290330@h54g2000cwb.googlegroups.com...
>>
>> 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.
|
| | | | |
Date: 26 Nov 2006 05:53:29
From: Chris L Peterson
Subject: Re: Decimalising RA and Dec Values
|
On Sun, 26 Nov 2006 15:08:23 +1000, burtond <burtond@westnet.com.au >
wrote:
>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
|
| |
Date: 23 Nov 2006 15:39:15
From: Eric
Subject: Re: Decimalising RA and Dec Values
|
Matt wrote:
> 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
|
| |
Date: 24 Nov 2006 16:28:09
From: canopus56
Subject: Re: Decimalising RA and Dec Values
|
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.
|
| |
Date: 24 Nov 2006 15:35:54
From: Matt
Subject: Re: Decimalising RA and Dec Values
|
> Have you considered breaking the values out into
> their own columns? (You can resize the columns
> to preserve the look for printout).
>
> One way to do this is to select the column of
> cells that contain the "compressed" formatted
> text, then on the tool bar select
>
> Data --> text to columns
>
> and follow the "wizard".
I had no idea this function existed - I'll give it a try as well...
Kind Regards,
Matt
|
| |
Date: 24 Nov 2006 14:44:22
From: Matt
Subject: Re: Decimalising RA and Dec Values
|
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
|
| | |
Date: 25 Nov 2006 11:19:44
From: Al
Subject: Re: Decimalising RA and Dec Values
|
<snip >
> 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...
:-)
|
| |
Date: 25 Nov 2006 14:19:22
From: canopus56
Subject: Re: Decimalising RA and Dec Values
|
dylan wrote:
<snip >
> 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
|
| | |
Date: 25 Nov 2006 16:32:13
From: Brian Tung
Subject: Re: Decimalising RA and Dec Values
|
canopus56 wrote:
> 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 <brian@isi.edu >
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
|
| | | |
Date: 26 Nov 2006 20:43:10
From: Paul Schlyter
Subject: Re: Decimalising RA and Dec Values
|
In article <ekanad$l6s$1@praesepe.isi.edu >, Brian Tung <brian@isi.edu> wrote:
> canopus56 wrote:
>> 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.
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.
--
----------------------------------------------------------------
Paul Schlyter, Grev Turegatan 40, SE-114 38 Stockholm, SWEDEN
e-mail: pausch at stockholm dot bostream dot se
WWW: http://stjarnhimlen.se/
|
| |
Date: 25 Nov 2006 11:50:37
From: dylan
Subject: Re: Decimalising RA and Dec Values
|
> 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 ?
|
|
