Solved

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

Posted on 2016-09-13
2
33 Views
Last Modified: 2016-09-13
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?
0
Comment
Question by:trevor1940
2 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 41796532
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
 
LVL 1

Author Closing Comment

by:trevor1940
ID: 41796876
Thanx very much
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question