LinkBack URL
About LinkBacks
Good Morning all,
What I am encountering is this, I want to change decimal degrees to DDMMSS. If a cell reads 87.6755, how would I have Excel to read it as 87degrees40min31.8sec? (87:40:31.8)
Thank you
Good Morning all,
What I am encountering is this, I want to change decimal degrees to DDMMSS. If a cell reads 87.6755, how would I have Excel to read it as 87degrees40min31.8sec? (87:40:31.8)
Thank you
Hi and welcome to the forum!
Can you explain what you mean by "have Excel to read it as"? For the purpose of what? Further calculations? What sort of further calculations will you be performing on this value?
Or do you mean that you wish to be able to enter 87.6755 in a cell and have a custom number format display it as 87:40:31.8?
Or do you mean that, say if A1 contains the numeric value 87.6755, you would like e.g. B1 to contain 87:40:31.8, and be content that this value is in text format (since no further calculations will be made with it)?
Regards
Last edited by XOR LX; 02-26-2014 at 09:25 AM.
I did a quick google search and found this:
http://fieldmuseum.org/users/jon-mar...g-dd-dms-excel
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thank you very much for the fast response! If you are up for a challenge, this is my challenge. I have developed a spreadsheet to use as a worksheet for calculating magnitude of inclination and bearing high point. The only calculations I need to complete it is an "IF/THEN" statement that achieves: "if the x value is + and the y value is +, then arctan(y/x), if x value is - and y value is +, then arctan(x/y)+90, if x value is - and y value is -, then arctan(y/x)+180, if x value is + and y value is -, the arctan(x/y)+270. The solution for this I need in DDMMSS format.
Thanks for your help!
Perhaps, with your x and y values in A1 and B1 respectively:
=TEXT(MOD(DEGREES(ATAN((B1/A1)^(-1^((B1*A1)<0))))+LOOKUP(SUMPRODUCT(10^{1,0},SIGN(A1:B1)),{-11,-9,9,11},90*{2,1,3,0}),360)/24,"[hh] mm ss")
though I can't believe there isn't a better way, mathematically, than first designating into one of your four categories.
Regards
See the ATAN2 function.
Entia non sunt multiplicanda sine necessitate
C2: =DEGREES(ATAN2(A2, B2))
This formula is so close its scary. As a check, I entered 0.0037 as my x value and -0.0255 as my y value. The solution of the formula is 261 44 39.
The correct solution is I believe is: atan(0.0037/-0.0255)=8.255888715=8 15 21.2(DMS)+270 00 00= 278 15 21.2
This works out on the Cartesian Graph if my math is correct. I'm adding the 8 15 21.2 to 270 00 00 b/c x value is positive and y value is negative.
@Bamagadbird
How do you get atan(0.0037/-0.0255)=8.255888715? Shouldn't that be -8.255888715?
@shg
Can't believe I just spent all that time, not even knowing of the existence of that formula!!
Regards
I dunno how you get that. It's ~ -81.7 degrees, or +278.3.Originally Posted by Bamagadbird
You are correct XOR LX. My over sight, but +278.3 is correct.
Thanks
But then your own formula is incorrect if you follow it through, as that too gives 261 44 39.
I only translated the logic you gave me...
Regards
You may be right...
Lets see if this makes sense
(+x,+y) = ATAN(y/x) + 0° This should graph in the I quadrant.
(-x,+y) = ATAN(x/y) + 90° This should graph in the II quadrant.
(-x,-y) = ATAN(y/x) + 180° This should graph in the III quadrant.
(+x,-y = ATAN(x\y) + 270° This should graph in the IV quadrant.
Does this make better sense?
You may be right...
Lets see if this makes sense
(+x,+y) = ATAN(y/x) + 0° This should graph in the I quadrant.
(-x,+y) = ATAN(x/y) + 90° This should graph in the II quadrant.
(-x,-y) = ATAN(y/x) + 180° This should graph in the III quadrant.
(+x,-y = ATAN(x\y) + 270° This should graph in the IV quadrant.
Does this make better sense?
Those conditions are precisely the same as those you gave originally...
I found it pal! This formula works.
=TEXT(MOD(DEGREES(ATAN((B1/A1)^(-1^((B1*A1)<0))))+LOOKUP(SUMPRODUCT(10^{1,0},SIGN(A1:B1)),{-11,-9,9,11},360*{2,1,3,0}),360)/24,"[hh] mm ss.0")
Thank you so much for putting me on the right path!
Wow ...
"Putting you on the right path"??!
Er, ok. Well, congratulations on your work!
Okay, here's the summary of what became of your greatness.
First, I had to format 4 seperate cells:
Cell 1:
=TEXT(MOD(DEGREES(ATAN((G12/F12)^(-1^((G12*F12)<0))))+LOOKUP(SUMPRODUCT(10^{1,0},SIGN(F12:G12)),{-11,-9,9,11},90*{2,1,3,0}),360)/24,"[hh] mm ss.0")
Cell 2:
=TEXT(MOD(DEGREES(ATAN((F12/G12)^(-1^((F12*G12)<0))))+LOOKUP(SUMPRODUCT(10^{1,0},SIGN(F12:G12)),{-11,-9,9,11},180*{2,1,3,0}),360)/24,"[hh] mm ss.0")
Cell 3:
=TEXT(MOD(DEGREES(ATAN((G12/F12)^(-1^((G12*F12)<0))))+LOOKUP(SUMPRODUCT(10^{1,0},SIGN(F12:G12)),{-11,-9,9,11},270*{2,1,3,0}),360)/24,"[hh] mm ss.0")
Cell 4:
=TEXT(MOD(DEGREES(ATAN((F12/G12)^(-1^((F12*G12)<0))))+LOOKUP(SUMPRODUCT(10^{1,0},SIGN(F12:G12)),{-11,-9,9,11},360*{2,1,3,0}),360)/24,"[hh] mm ss.0")
Then I had to format a cell with the IF,Then,And statement:
=IF(AND(F12>=0,G12>=0),A19,IF(AND(F12<0,G12>=0),B19,IF(AND(F12<0,G12<0),C19,IF(AND(F12>=0,G12<0),D19))))
This achieved my final goal.
Thanks Guys!!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
It is also customary to click on the Add reputation button of those that helped you as a nice way of saying thank you to them.
Using =degrees(atan2(a1, b1)) instead of all that was too complicated?
how do you convert this to DMS? This appears to be very simple. The formulae I reflected back resulting from the input given by XOR LX works flawlessly in the worksheet I developed for our survey process, but I have a use for =degrees(atan2(a1, b1)) in another task ahead of me.
C2: =DEGREES(ATAN2(A2, B2)) Format of C2: 0.00000 C7: =MOD(DEGREES(ATAN2(A7, B7)), 360) / 24 Format of C7: [h]:mm:ss.0
Last edited by shg; 03-03-2014 at 06:17 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks