Converting Decimal Degrees to D:M:S in excel Part 2

This is a follow on qquestion from Converting Decimal Degrees to D:M:S in excel

There is a rounding error in The accepted solution

=CONCATENATE(TEXT(INT(ABS(A2)),"000"),":",TEXT(INT(ROUND(MOD(ABS(A2),1),2)*60),"00"),":",TEXT(ROUND(MOD(ROUND(MOD(ABS(A2),1),2)*60,1),2)*60,"00"),":",IF(A2<0,"S","N"),"/",TEXT(INT(ABS(J2)),"000"),":",TEXT(INT(ROUND(MOD(ABS(J2),1),2)*60),"00"),":",TEXT(ROUND(MOD(ROUND(MOD(ABS(J2),1),2)*60,1),2)*60,"00"),":",IF(J2<0,"W","E"))

Open in new window


Given these values in A2 & B2

78.004      123.997       I get  this 78:00:00:N/123:60:00:E

78.004      should be 78:00:14.4
123.997      should be 123:59:49.2


78:00:14:N/123:59:49:E

Any ideas how I amend the formula?
LVL 1
trevor1940Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
The ROUND function limits your numbers to 2 decimal places and is causing the inaccuracy. Change the round functions to use 3 decimal places to add precision.
Then change the TEXT function for the seconds part to output the values as 00.0.

Here is the updated function.

=CONCATENATE(TEXT(INT(ABS(A2)),"000"),":",TEXT(INT(ROUND(MOD(ABS(A2),1),3)*60),"00"),":",TEXT(ROUND(MOD(ROUND(MOD(ABS(A2),1),3)*60,1),3)*60,"00.0"),":",IF(A2<0,"S","N"),"/",TEXT(INT(ABS(J2)),"000"),":",TEXT(INT(ROUND(MOD(ABS(J2),1),3)*60),"00"),":",TEXT(ROUND(MOD(ROUND(MOD(ABS(J2),1),3)*60,1),3)*60,"00.0"),":",IF(J2<0,"W","E"))

Open in new window

0
 
trevor1940Author Commented:
Thanx very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.