 # Converting Decimal Degrees to D:M:S in excel

Can someone explain why I'm getting rounding errors in the attached spread sheet?  DDtoDMS.xlsx

I'm expecting column 'T' to look like this

``````42:33:00:N/001:34:59.88:W
``````

If E3 =0.55 and F3 = "E3*60"/33 so how can G3 = "INT(F3)" / 32?

My maths might be dodgy but 0.55 *60 = 33

Also how do I fix the leading zeros in the output?
Microsoft Excel Last Comment
trevor1940 Rob Henson That is bizarre to say the least!!

If you copy E2 and paste values elsewhere, it gives 0.549999999999997 hence why that value multiplied by 60 doesn't give an exact 33.

Change the formula in E2 to:

=ROUND(C2-D2,2)

That sorts it.

I don't see any leading zeros in the output.

Thanks
Rob H Rob Henson To force the numbers to show as a certain number of digits, you can use the TEXT function, your end result formula becomes:

=CONCATENATE(TEXT(D2,"000"),":",TEXT(G2,"00"),":",TEXT(I2,"00"),":",B2,"/",TEXT(M2,"000"),":",TEXT(P2,"00"),":",TEXT(R2,"00"),":",K2)

Change the number of 0 in the " " to affect how the number appears. Rob Henson  THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION  THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform. trevor1940

Hi Rob

If you copy E2 and paste values elsewhere, it gives 0.549999999999997 hence why that value multiplied by 60 doesn't give an exact 33.

Change the formula in E2 to:

=ROUND(C2-D2,2)

Any idea why this happening?

Thanx for your 1 cell solution very neat I had opened up my solution to work where this error was

BTW Latitude can only be 2 digits for degrees only saying

@crystal

use the ROUND function on values in column I and R. Internally,

How will that help?  hasn't the damage already been made at this point?

I don't understand how 42.55 - 42 doesn't = 0.55?  is there a format setting some where causing this error? Rob Henson Crystal has answered the question as why Excel is doing this, much as it is annoying I don't think there is a lot that can be done about it other than allowing for it with ROUND functions. trevor1940

Thanx for you help and explanation Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY           