Formula help when entering a neg longitude (West)

**BigGPL** · 01-23-2012, 10:15 AM

Morning,

I'm using the formula provided by C. Pearson to convert degree minute seconds into degress decimal and vice versa.

The problem I am having is that when you try to convert a degree decimal longitude that is west of the meridian using a -(minus sign) back to degree minute seconds there results an error.

This is the formula for the array that needs to be able to handle a negative number:

{={1,0,0}*INT(E14)+{0,1,0}*MINUTE(E14/24)+{0,0,1}*SECOND(E14/24)}

Any ideas as to how to remedy this?

As usual any help is greatly appreciated.

George

**NBVC** · 01-23-2012, 10:20 AM

Possibly rap the cell references in ABS()?

e.g.

={1,0,0}*INT(ABS(E14))+{0,1,0}*MINUTE(ABS(E14)/24)+{0,0,1}*SECOND(ABS(E14)/24)

**BigGPL** · 01-23-2012, 11:18 AM

Hey NBVC,

Thanks. That took care of the errors, however the problem remains that if you enter -81.8550 in cell R14, which is somewhere near Cleveland Hopkins Int'l Airport, the result in R19, S19, T19, comes out as 81degrees 51min 18sec which puts you somewhere in China. Need to have the minus sign in the results.

George

**NBVC** · 01-23-2012, 11:26 AM

Are all 3 numbers supposed to turn negative (sorry, not a geographer)?

If so,

=IF(E14<0,-({1,0,0}*INT(ABS(E14))+{0,1,0}*MINUTE(ABS(E14)/24)+{0,0,1}*SECOND(ABS(E14)/24)),{1,0,0}*INT(E14)+{0,1,0}*MINUTE(E14/24)+{0,0,1}*SECOND((E14/24)))

**BigGPL** · 01-23-2012, 02:05 PM

Not all the numbers, just the first set. And to be clear, if the longitude is west of the the prime meridian (Greenwich, England) then the number would be displayed as either -81.8550 or W81.8550. If east of the meridian the the display would be 81.8550 or E81.550.

For example the ;
Latitude 41.4094, Longitude 81.8550 places you in Xin He Xian, Akesu, Xinjiang, China

where as;
Latitude 41.4094, Longitude -81.8550 would place you on Hanger Rd. in Cleveland, Oh.

Anyway, only the first set of numbers would either have a -(minus sign) or no sign at all depending on if the longitude to be converted had minus sign or not.

I hope this sort of clears things up a bit.

**NBVC** · 01-23-2012, 02:17 PM

Ok let's try this.

Select E19:G19 and delete the array formula.

Now enter this regular formulas

in E19:

=INT(ABS(E14))*SIGN(E14)

in F19:

=MINUTE(ABS(E14)/24)

in G19:

=SECOND(ABS(E14)/24)