Converting Coordinates in Excel

[Johnny Blood]

I need some help coming up with an Excel formula for converting coordinates
(dd-mm-ss) to a digital format. Can someone help? I have a start to the
formula, but something is wrong. Here is what I have started already
(keeping in mind something is wrong):

=Latitude Degrees+Latitude Minutes/60+Latitude Seconds/3600
=Longitude Degrees+Longitude Minutes/60+Longitude Seconds/3600

....it appears in my Excel Spreadsheet as

=A2+B2/60+C2/3600
=D2+E2/60+F2/3600

What am I missing?

Thanks,

Johnny Blood

 

[jaf]

Don't know. Looks OK to me.

15 30 0 15.5 30 0
16

 

[Ken Wright]

Check out Chip's site:-

http://cpearson.com/excel/latlong.htm

 

[Johnny Blood]

No, I must be doing something wrong. For example, I have the coordinates
43-04-25 & -89-23-07. The same document shows the accurate and correct
digital coordinates as 43.07365 & -89.38515 respectively. However, when I
use the formula below in Excel, I get the following coordinates (which are
way off): 43.07361 & -88.61472

Todd

 

[Johnny Blood]

Forgive my ignorance, but I'm confused by the information contained in this
link that says, "Excel's trigonometric functions work only with Radians, not
Degrees, so be sure to convert angular values to radians with the =RADIANS
function."

What exactly am I supposed to do here?

Todd

 

[Dave Peterson]

I think that your formula is fine for positive coordinates. But for negative
values (like your -89,23,07), you have a problem.

I bet you want -89,23,07 to be more than 89 degrees. Your formula is actually
reducing it. -89+something positive + something else positive.

Depending on how you enter the values, (maybe just negative numbers in the
degrees cell), you could do this:

=SIGN(A3)*(ABS(A3)+B3/60+C3/3600)

Or if you entered
-89, -23, -7
you'd be ok, too.

And I think your answers are correct.

Have you verified your $500 excel calculations on your $1000 pc with a $1.00
calculator?

 

[Dana DeLouis]

I think Dave is correct in that you are taking a -89, then "adding" +
23/60, etc.

However, is the document correct in the numbers? The reason I ask it that
it appears to be off.
I get:
43.0736111111111
89.3852777777778

Ignoring the minus sign, you show:
43.07365
89.38515

I don't see any pattern in the documented solution that would suggest a
different function.

Sub test()
Dim n1
Dim n2

n1 = 43 + 4 / 60 + 25 / 3600
n2 = 89 + 23 / 60 + 7 / 3600

Debug.Print n1
Debug.Print n2
End Sub

 

[Dave Peterson]

And I think your answers are correct.

I read Dana's post and reread mine. I meant that your answers
obtained in excel look right to me.

(My original message was pretty unclear about which answers I thought
were correct.)