September 14, 2004

5:16 PM

How do I format cells in EXCEL to accept numeric values in the form: -26:32 (or +26:32 ... that is, mm.ss but including the pos/neg sign)? I'm trying to compile a simply list of Messier Objects (astronomy) and can't figure out how to include declinations.
posted by RavinDave to (13 comments total)

Why not divide the declination column into two coulmns - one for minutes and the other for seconds? Then use signed scalars for both columns.
posted by b1tr0t at 5:51 PM on September 14, 2004

Well, it'd work ... but it'd make calculations messy, wouldn't it?

(I'm open to the acusation that I'm missing something REAL simple in EXCEL, of course) ;)
posted by RavinDave at 6:00 PM on September 14, 2004

Google gives a few promising looking results....
posted by Space Coyote at 6:01 PM on September 14, 2004

You could always highlight the column and format the text. Under the number tab, you change the column to read text and it will keep exactly what you type. Only problem is that you won't be able to use any calculations with it., but it would show the plus and minus. There are ways to write your own format under custom (I think), but I can't figure it out.
posted by rodz at 7:57 PM on September 14, 2004

Space Coyote I found most of those pages. Unfortunately, they are more concerned with providing formulae using "declination" (and such) -- no so much concerned with how to format cells to display it properly. Valuable stuff - I bookmarked several, but I'm still flummoxed.
posted by RavinDave at 8:11 PM on September 14, 2004

I don't know how to do exactly what you want it to do, but two quick workarounds would be either to convert everything to decimal degrees — you could even use formulas such as = -(26 + 32/60) — or, if you've got horizontal real estate to burn, use three columns: one containing degrees, one containing minutes, and a third containing a formula not unlike = (degrees + minutes/60). Then use the third one for calculations.

I assume you've already looked up the documentation for the "custom" formats.
posted by Johnny Assay at 9:31 PM on September 14, 2004

I assume you've already looked up the documentation ...

I'm close to it, Johnny. I know that one accesses the FORMAT/CELLS/NUMBER menu, picks something close, and edit it in the "TYPE:" box. EXCEL saves it.

This is pretty straightforward for simple things (eg: adding a label, formating decimal places, etc.), but it is trickier when it comes to conditionals (eg: add a "+" OR "-"). I'm trying to run it down; reading the docs and googling. So far, not much luck. But I'm getting closer.

Thanx!
posted by RavinDave at 9:55 PM on September 14, 2004

I assume you've already looked up the documentation for the "custom" formats.

On my Macintosh edition of Excel the documentation for "custom" formats says "pick one that looks like you want and edit."
posted by b1tr0t at 10:12 PM on September 14, 2004

this page describes how to access the underlying value (which is fractions of a day!). you might try editing that value to be negative and seeing what happens.
posted by andrew cooke at 5:22 AM on September 15, 2004

ah no, says here that excel won't display negative times.

i think you're going to have to store the column as decimal and then generate the formatetd value as text in another column via a formula. i don't know enough about excel to know how you'd go in the other direction (allow time to be entered and converted automatically) though.
posted by andrew cooke at 5:27 AM on September 15, 2004

if you include an apostrophe before whatever you're typing, this forces excel to keep it as you type it, if you know what i mean. I've just done a quick sample in a spreadsheet, and simple addition and subtraction formulae work even with the apostrophe in there. Hope that helps.
posted by kev23f at 5:57 AM on September 15, 2004

If you can separate your values into two columns, with minute values in column A, and second values in column B, both formatted as "General" try using the following formula in column C:

=IF(A1>0,"+"&+A1&+":"&+B1,+A1&+":"&+B1)

This solves the display problem, but I'm not sure about calculations. Better to do calculations on the original values (which could be in hidden columns if needed).
posted by grateful at 7:38 AM on September 15, 2004

Declination is given in positive and negative degrees (and sometimes minutes and seconds) of arc. It's right ascension (and hour angle) that are given in hours, minutes and seconds of time.

You might be able to work up a solution from this example.
posted by Songdog at 8:37 AM on September 15, 2004