trevor1940
asked on
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
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?
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?
Last Comment
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"
Change the number of 0 in the " " to affect how the number appears.
=CONCATENATE(TEXT(D2,"000"
Change the number of 0 in the " " to affect how the number appears.
ASKER CERTIFIED 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.
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.
ASKER
Hi Rob
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
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?
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?
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.
ASKER
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
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